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:

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

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.