Forum, Tips & Tricks

Learn tips and tricks and engage in discussions with our blog authors here. It’s all for your benefit and learning. 

You need to log in to create posts and topics.

Calculated column - Sumifs (how to create excel function sumif in Power BI)

Problem:

Let's assume that you are a proficient user of MS Excel and you want to create some nice visualization in Power Bi.  You probably used sumif() / sumifs() functions in MS Excel which, together with vlookup() and if(), are probably the most useful functions in MS excel.

Solution:

In data part you add new calculated column.

Code:

Col_name = CALCULATE(SUM(table_name[column_that_you want to sum]);

FILTER(table_name; table_name [date] <= EARLIER(table_name [date])))

 

·        The part with FILTER creates sumif-like effect. Function EARLIER returns the current value of the specified column in an outer evaluation pass of the mentioned column. That means that for each column Power BI looks at current date and sums all columns with date smaller or equal of the current column.

·        Earlier: https://docs.microsoft.com/en-us/dax/earlier-function-dax

 

Case study

Input: trial balances by month consisting of plans and reality. Desired output is Line graph by month with Legend in format ( year + Reality/Plan) showing cumulative revenue.

Preparation:

First of all I have calculated condition column in Power Query M where I simply merged year and type of  trial balance ( real/ plan). [merged]

Note: I have also created Conditional column is_line ( which is true for all columns -real and true for plan for the last year in database)

Conditional column DAX:

Revenue_cumul = CALCULATE(SUM(table_name[Sum_column]);

FILTER(table_name; table_name[merged] = earlier(table_name[merged]));

FILTER(table_name; table_name[date] <= EARLIER(table_name [date]));                                                                      table_name[is_line] ="True" )

Explanation:

I have two conditions one for date and second for merged category and year. My date from trial balance is in by accounts therefore when you put column Revenue_cumul in Visual input you have to select average. (the default is sum; which sums your calculated cumulative sum by every account -> the result would not be correct)

 

 

Line graph with basic sum:

Line Graph with our calculated column

 

Uploaded files:
  • pic1.png
  • pic2.png