Forum Discussion
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.
| Year | CostCenter | Exp-Inc | Month01 | Month02 | Month03 |
| 2017 | Company A | Sales | 100 | 150 | 124 |
| 2017 | Company A | Salary | 50 | 50 | 50 |
| 2017 | Company A | Other costs | 20 | 20 | 20 |
| 2017 | Company B | Sales | 150 | 225 | 186 |
| 2017 | Company B | Salary | 75 | 75 | 75 |
| 2017 | Company B | Other costs | 30 | 30 | 30 |
| 2018 | Company A | Sales | 120 | 180 | 149 |
| 2018 | Company A | Salary | 60 | 60 | 60 |
| 2018 | Company A | Other costs | 24 | 24 | 24 |
| 2018 | Company B | Sales | 188 | 281 | 233 |
| 2018 | Company B | Salary | 94 | 94 | 94 |
| 2018 | Company B | Other costs | 38 | 38 | 38 |
The wish is to get calculated values in rows by each year, company(CostCenter) and month.
It should look like:
| Year | CostCenter | Exp-Inc | Month01 | Month02 | Month03 |
| 2017 | Company A | Sales | 100 | 150 | 124 |
| 2017 | Company A | Salary | 50 | 50 | 50 |
| 2017 | Company A | Profit before other costs | 50 | 100 | 74 |
| 2017 | Company A | % | 50% | 67% | 60% |
| 2017 | Company A | Other costs | 20 | 20 | 20 |
| 2017 | Company A | Profit | 30 | 80 | 54 |
| 2017 | Company A | % | 30% | 53% | 44% |
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_MonthNumbThe result is a table looking like this:
After loading this to your Data Model you have to create a Dimension Table (DimTable):
Exp-IncID ID Sales 1 Salary 2 Profit before other costs 3 % PBOC 4 Other costs 5 Profit 6 % Profit 7 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:
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.
4 Replies
FloriankxSolution Sage
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_MonthNumbThe result is a table looking like this:
After loading this to your Data Model you have to create a Dimension Table (DimTable):
Exp-IncID ID Sales 1 Salary 2 Profit before other costs 3 % PBOC 4 Other costs 5 Profit 6 % Profit 7 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:
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.
Thank you for your answer. Bookmarked it in my browser :robothappy:
... 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?
- Floriankx
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.
