topic Re: multi levels calculations in DAX Commands and Tips

multi levels calculations

nicnic38 — Fri, 29 Nov 2024 10:34:20 GMT

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 has 3 levels : group, subgroup1, subgroup2.

in my exemple, i put :

-2 groups (but there will be 50) : B is the reference group

- theses groups are divided into 3 groups (subgroup1)

- inside subgroup1 there are 4 groups (subgroup2).

S1 : My first calculations (total) :

step 1 : attribute the average price (total price / sales) of A to the sales of B :

step 2 : calculate the theorical total price of A

step 3 : calculate the difference between the theorical total price of A and the total price of B

S2 : My seconds calculations (by subgroup):

step 4 : attribute the average price value of subgroup of A (2 and 3) to the sales of B (2 and 3)

step 5 : sum each total of the subgroup

step 6 : calculate the difference between the total of subgroup A and the total of subgroup B

S3 : My thirds calculations

step 7 : calculate the difference between my 2 first calculations

step 8 : keep only one line per group (A and B).

thanks in advance for help.

here is the file (simplifyed) to work with : https://drive.google.com/file/d/1i9SYQE0fllQw2Bs6NxglxXTgxUPy0MHU/view?usp=sharing

Re: multi levels calculations

lbendlin — Wed, 27 Nov 2024 21:51:17 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: multi levels calculations

nicnic38 — Thu, 28 Nov 2024 08:23:26 GMT

post updated : file added

Re: multi levels calculations

Anonymous — Thu, 28 Nov 2024 08:25:56 GMT

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

Re: multi levels calculations

nicnic38 — Thu, 28 Nov 2024 09:04:30 GMT

look great... for my sample.

my dataset is much more complex :

- "group" has 50 values

- "subgroup1" has more than 200 values

- "subgroup2" has from 4 to 10 values dependnaing on the value of subgroup1

thanks again for help

Re: multi levels calculations

lbendlin — Thu, 28 Nov 2024 13:26:08 GMT

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: multi levels calculations

nicnic38 — Fri, 29 Nov 2024 07:43:59 GMT

here is the dataset : https://drive.google.com/file/d/1DS44dI23jA_YRSB_JqXSiGyqa4J5HmUW/view?usp=drive_link

Re: multi levels calculations

lbendlin — Thu, 28 Nov 2024 15:57:45 GMT

Access denied.

Re: multi levels calculations

nicnic38 — Thu, 28 Nov 2024 16:07:47 GMT

the data file is updated

Re: multi levels calculations

lbendlin — Thu, 28 Nov 2024 18:11:28 GMT

still access denied - please check the sharing settings.

Re: multi levels calculations

nicnic38 — Fri, 29 Nov 2024 07:44:22 GMT

link updated

thanks

Re: multi levels calculations

nicnic38 — Fri, 29 Nov 2024 08:55:31 GMT

https://drive.google.com/file/d/1DS44dI23jA_YRSB_JqXSiGyqa4J5HmUW/view?usp=sharing

Re: multi levels calculations

lbendlin — Fri, 29 Nov 2024 22:33:20 GMT

step 1 : attribute the average price (total price / sales) of A to the sales of B :

I see that you have lines with multiple sales. That means you cannot just divide the total price by sales. You would have to identify the price of an individual sale and then average that out across all sales.

What is your definition of "average price" and why?

Re: multi levels calculations

nicnic38 — Tue, 03 Dec 2024 11:33:35 GMT

average price = total price / sales for each group (A, B, C, D).

the aim is to "standardized" the total price by using the reference of group B.

You will have the total price of A or C or D if you have the sales of B

Re: multi levels calculations

nicnic38 — Tue, 03 Dec 2024 15:08:43 GMT

I finalize the calculation 1 :

Sales(B) 1 =
var mes_racines = ALLSELECTED(data_web2024m6[subgroup1])
var mes_durees = ALLSELECTED(data_web2024m6[var_d])
var mes_annees = ALLSELECTED(data_web2024m6[annee])
var mes_adultes = ALLSELECTED(data_web2024m6[var_a])
var mes_hosp = ALLSELECTED(data_web2024m6[var_h])
var mes_urg = ALLSELECTED(data_web2024m6[var_u])
var _totalSales_B =
CALCULATE(SUM(data_web2024m6[sales]),data_web2024m6[group] = "B",
data_web2024m6[subgroup1] in mes_racines,
data_web2024m6[var_a] in mes_adultes,
data_web2024m6[var_h] in mes_hosp,
data_web2024m6[annee] in mes_annees,
data_web2024m6[var_d] in mes_durees,
data_web2024m6[var_u] in mes_urg,
ALLEXCEPT(data_web2024m6,data_web2024m6[group]))
VAR _totalSales_all = CALCULATE(SUM(data_web2024m6[sales]),
data_web2024m6[subgroup1] in mes_racines,
data_web2024m6[var_a] in mes_adultes,
data_web2024m6[var_h] in mes_hosp,
data_web2024m6[annee] in mes_annees,
data_web2024m6[var_d] in mes_durees,
data_web2024m6[var_u] in mes_urg,
ALLEXCEPT(data_web2024m6,data_web2024m6[group]))
RETURN
IF(SELECTEDVALUE(data_web2024m6[group]) <> "B",_totalSales_B,_totalSales_all)

pmct(B) 1 =
var mes_racines = ALLSELECTED(data_web2024m6[subgroup1])
var mes_durees = ALLSELECTED(data_web2024m6[var_d])
var mes_annees = ALLSELECTED(data_web2024m6[annee])
var mes_adultes = ALLSELECTED(data_web2024m6[var_a])
var mes_hosp = ALLSELECTED(data_web2024m6[var_h])
var mes_urg = ALLSELECTED(data_web2024m6[var_u])
VAR pmct1 = CALCULATE(CALCULATE(SUM(data_web2024m6[total_price]),
data_web2024m6[subgroup1] in mes_racines,
data_web2024m6[var_a] in mes_adultes,
data_web2024m6[var_h] in mes_hosp,
data_web2024m6[var_d] in mes_durees,
data_web2024m6[annee] in mes_annees,
data_web2024m6[var_u] in mes_urg,
ALLEXCEPT(data_web2024m6,data_web2024m6[group]))
/
CALCULATE(SUM(data_web2024m6[sales]),
data_web2024m6[subgroup1] in mes_racines,
data_web2024m6[var_a] in mes_adultes,
data_web2024m6[var_d] in mes_durees,
data_web2024m6[var_h] in mes_hosp,
data_web2024m6[annee] in mes_annees,
data_web2024m6[var_u] in mes_urg,
ALLEXCEPT(data_web2024m6,data_web2024m6[group])))
RETURN
pmct1

calculation1 =
VAR _A_calculate = CALCULATE([valo_std(B) 1])
VAR _B_calculate = CALCULATE([valo_std(B) 1],data_web2024m6[group] = "B")
RETURN
_A_calculate - _B_calculate

Re: multi levels calculations

nicnic38 — Thu, 05 Dec 2024 07:37:17 GMT

still calculation 2 ans calculation 3 to do 😞