Calculate grouped values based on criteria

[Girts]

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%

[Floriankx · Accepted solution]

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:

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.

[Girts]

Thank you for your answer. Bookmarked it in my browser 

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

[Girts]

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%