cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Girts Frequent Visitor
Frequent Visitor

Calculate grouped values based on criteria

I am new in Power BI, read a lot of different solutions in web, but couldn't get a result. 

Source data comes from financial accounts + analytical dimensions. 
For this post created table with dummy values.

YearCostCenterExp-IncMonth01Month02Month03
2017Company ASales100150124
2017Company ASalary505050
2017Company AOther costs202020
2017Company BSales150225186
2017Company BSalary757575
2017Company BOther costs303030
2018Company ASales120180149
2018Company ASalary606060
2018Company AOther costs242424
2018Company BSales188281233
2018Company BSalary949494
2018Company BOther costs383838

 

The wish is to get calculated values in rows by each year, company(CostCenter) and month. 
It should look like: 

YearCostCenterExp-IncMonth01Month02Month03
2017Company ASales100150124
2017Company ASalary505050
2017Company AProfit before other costs5010074
2017Company A%50%67%60%
2017Company AOther costs202020
2017Company AProfit308054
2017Company A%30%53%44%
1 ACCEPTED SOLUTION

Accepted Solutions
Floriankx Established Member
Established Member

Re: Calculate grouped values based on criteria

Hello,

 

you know for starters it is a really hard request.

 

First Step: You have to Unpivot your FactTable using PowerQuery

let
    Source = Excel.CurrentWorkbook(){[Name="FactTable"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"CostCenter", type text}, {"Exp-Inc", type text}, {"Month01", Int64.Type}, {"Month02", Int64.Type}, {"Month03", Int64.Type}}),
    Unpivot_Month = Table.UnpivotOtherColumns(#"Changed Type", {"Exp-Inc", "CostCenter", "Year"}, "Attribut", "Wert"),
    Add_MonthNumb = Table.AddColumn(Unpivot_Month, "MonthNumb", each Text.End([Attribut],2)),
    Change_MonthNumb = Table.TransformColumnTypes(Add_MonthNumb,{{"MonthNumb", Int64.Type}})
in
    Change_MonthNumb

The result is a table looking like this:

image.png

 

After loading this to your Data Model you have to create a Dimension Table (DimTable):

 

Exp-IncIDID
Sales1
Salary2
Profit before other costs3
% PBOC4
Other costs5
Profit6
% Profit7

 

In your DimTable you create the following Measure:

Value:=
IF(HASONEVALUE(DimTable[Exp-Inc]); VAR Sales=CALCULATE(SUM(FactTable[Wert]);FactTable[Exp-Inc]="Sales") VAR Salary=CALCULATE(SUM(FactTable[Wert]);FactTable[Exp-Inc]="Salary") VAR Others=CALCULATE(SUM(FactTable[Wert]);FactTable[Exp-Inc]="Other costs") VAR Gross_Profit=Sales-Salary VAR Switch_Measure= SWITCH(MAX(DimTable[ID]); 1;Sales; 2;Salary; 3;Gross_Profit; 4;DIVIDE(Gross_Profit;Sales); 5;Others; 6;Gross_Profit-Others; 7;DIVIDE((Gross_Profit-Others);Sales)) RETURN IF(OR(MAX(DimTable[ID])=4;MAX(DimTable[ID])=7); FORMAT(Switch_Measure;"0%"); FORMAT(Switch_Measure;"0")); BLANK())

It may be possible you have to change ";" to ",", according to my non-englisch version.

 

The result is the following:

image.png

 

There are dozens of great posts and blogs explaining SWITCH and VAR like:
Paramter Table, VAR 1, VAR 2, and my previous blog for SWITCH.

 

You could also try to solve your problem through cascading like here.

 

Best regards.

View solution in original post

4 REPLIES 4
Floriankx Established Member
Established Member

Re: Calculate grouped values based on criteria

Hello,

 

you know for starters it is a really hard request.

 

First Step: You have to Unpivot your FactTable using PowerQuery

let
    Source = Excel.CurrentWorkbook(){[Name="FactTable"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"CostCenter", type text}, {"Exp-Inc", type text}, {"Month01", Int64.Type}, {"Month02", Int64.Type}, {"Month03", Int64.Type}}),
    Unpivot_Month = Table.UnpivotOtherColumns(#"Changed Type", {"Exp-Inc", "CostCenter", "Year"}, "Attribut", "Wert"),
    Add_MonthNumb = Table.AddColumn(Unpivot_Month, "MonthNumb", each Text.End([Attribut],2)),
    Change_MonthNumb = Table.TransformColumnTypes(Add_MonthNumb,{{"MonthNumb", Int64.Type}})
in
    Change_MonthNumb

The result is a table looking like this:

image.png

 

After loading this to your Data Model you have to create a Dimension Table (DimTable):

 

Exp-IncIDID
Sales1
Salary2
Profit before other costs3
% PBOC4
Other costs5
Profit6
% Profit7

 

In your DimTable you create the following Measure:

Value:=
IF(HASONEVALUE(DimTable[Exp-Inc]); VAR Sales=CALCULATE(SUM(FactTable[Wert]);FactTable[Exp-Inc]="Sales") VAR Salary=CALCULATE(SUM(FactTable[Wert]);FactTable[Exp-Inc]="Salary") VAR Others=CALCULATE(SUM(FactTable[Wert]);FactTable[Exp-Inc]="Other costs") VAR Gross_Profit=Sales-Salary VAR Switch_Measure= SWITCH(MAX(DimTable[ID]); 1;Sales; 2;Salary; 3;Gross_Profit; 4;DIVIDE(Gross_Profit;Sales); 5;Others; 6;Gross_Profit-Others; 7;DIVIDE((Gross_Profit-Others);Sales)) RETURN IF(OR(MAX(DimTable[ID])=4;MAX(DimTable[ID])=7); FORMAT(Switch_Measure;"0%"); FORMAT(Switch_Measure;"0")); BLANK())

It may be possible you have to change ";" to ",", according to my non-englisch version.

 

The result is the following:

image.png

 

There are dozens of great posts and blogs explaining SWITCH and VAR like:
Paramter Table, VAR 1, VAR 2, and my previous blog for SWITCH.

 

You could also try to solve your problem through cascading like here.

 

Best regards.

View solution in original post

Girts Frequent Visitor
Frequent Visitor

Re: Calculate grouped values based on criteria

Thank you for your answer. Bookmarked it in my browser Robot Happy

 

... modified ... Why did you add a custom column for MonthNumber?

 

... about variable Switch_Measure - As I understand, it was used to get right format for % values?

Highlighted
Floriankx Established Member
Established Member

Re: Calculate grouped values based on criteria

Hello,

 

MonthNumb is obsolete, I expected it to be needed but it wasn't the case.

 

Switch was used to get the required structure of Values. Because some are calculated and others aren't.

 

The Format I defined in the IF-Statement.

 

Best regards.

Girts Frequent Visitor
Frequent Visitor

Re: Calculate grouped values based on criteria

Sorry that I didn't open a new topic - can you help me to add one more modification to this solution - I need to add a custom column which shows grow rate vs previous month.  

Like an example for Company A, 2017, Sales month03 grow rate vs month02 is (124-150)/150= -17%

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 281 members 2,943 guests
Please welcome our newest community members: