Forum Discussion

nicnic38's avatar
nicnic38
Frequent Visitor
1 year ago

multi levels calculations

hello,   i'd like to calculate complex (for me) calculation to obtain specific results. The aim is to avoid temporary table in order to calculate whatever the selection of the data is.   My data...
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi, nicnic38 

Thanks for lbendlin's reply. You can try following measure to solve your need.

 

 

average price = 
VAR _group = SELECTEDVALUE('Table'[Group])
VAR _subgroup1 = SELECTEDVALUE('Table'[Subgroup1])
VAR _subgroup2 = SELECTEDVALUE('Table'[Subgroup2])
VAR _sales = SELECTEDVALUE('Table'[Sales])
VAR _totalProce = CALCULATE(SUM('Table'[Total price]),ALLEXCEPT('Table','Table'[Group],'Table'[Subgroup1]))
RETURN
VAR _totalSales = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Group],'Table'[Subgroup1]))
RETURN
DIVIDE(_totalProce,_totalSales)

average price diff_group = 
VAR _group = SELECTEDVALUE('Table'[Group])
VAR _subgroup1 = SELECTEDVALUE('Table'[Subgroup1])
VAR _subgroup2 = SELECTEDVALUE('Table'[Subgroup2])
VAR _sales = SELECTEDVALUE('Table'[Sales])
VAR _totalProce = CALCULATE(SUM('Table'[Total price]),ALLEXCEPT('Table','Table'[Group]))
RETURN
VAR _totalSales = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Group]))
RETURN
DIVIDE(_totalProce,_totalSales)

Sales(B) 1 = 
VAR _totalSales_B = CALCULATE(SUM('Table'[Sales]),'Table'[Group] = "B",ALLEXCEPT('Table','Table'[Group]))
VAR _totalSales_all = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Group]))
RETURN
IF(SELECTEDVALUE('Table'[Group]) = "A",_totalSales_B,_totalSales_all)

Sales(B) 2 = 
VAR _totalSales_B = CALCULATE(SUM('Table'[Sales]),'Table'[Group] = "B",ALLEXCEPT('Table','Table'[Group],'Table'[Subgroup1]))
VAR _totalSales_all = CALCULATE(SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[Group],'Table'[Subgroup1]))
RETURN
IF(SELECTEDVALUE('Table'[Group]) = "A",_totalSales_B,_totalSales_all)

Calculation 1 = 
VAR _averagePrice_Sales = [average price diff_group] * [Sales(B) 1]
VAR _A_calculate = CALCULATE([average price diff_group] * [Sales(B) 1],'Table'[Group] = "A")
VAR _B_calculate = CALCULATE([average price diff_group] * [Sales(B) 1],'Table'[Group] = "B")
RETURN
_A_calculate - _B_calculate

Calculation 2 = 
VAR _calculate2 = [average price] * [Sales(B) 2]
VAR _A_2_calculate = CALCULATE([average price] * [Sales(B) 2],'Table'[Group] = "A" && 'Table'[Subgroup1] = 2)
VAR _A_3_calculate = CALCULATE([average price] * [Sales(B) 2],'Table'[Group] = "A" && 'Table'[Subgroup1] = 3)
VAR _B_2_calculate = CALCULATE([average price] * [Sales(B) 2],'Table'[Group] = "B" && 'Table'[Subgroup1] = 2)
VAR _B_3_calculate = CALCULATE([average price] * [Sales(B) 2],'Table'[Group] = "B" && 'Table'[Subgroup1] = 3)
RETURN
_A_2_calculate + _A_3_calculate - _B_2_calculate - _B_3_calculate

Calculation 3 = [Calculation 2] - [Calculation 1]



Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum