cancel
Showing results for
Did you mean:
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.

 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%
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage

## 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"),
in
Change_MonthNumb```

The result is a table looking like this:

 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 4
Highlighted
Solution Sage

## 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"),
in
Change_MonthNumb```

The result is a table looking like this:

 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.

Frequent Visitor

## Re: Calculate grouped values based on criteria

... 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?

Solution Sage

## 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.

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%

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors