multi levels calculations

nicnic38 · ‎11-27-2024

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

nicnic38 · ‎12-04-2024

still calculation 2 ans calculation 3 to do 😞

nicnic38 · ‎12-03-2024

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

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)

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

nicnic38 · ‎11-29-2024

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

lbendlin · ‎11-29-2024

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?

nicnic38 · ‎12-02-2024

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

nicnic38 · ‎11-28-2024

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

lbendlin · ‎11-28-2024

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

nicnic38 · ‎11-28-2024

the data file is updated

lbendlin · ‎11-28-2024

still access denied - please check the sharing settings.

nicnic38 · ‎11-28-2024

link updated

thanks

nicnic38 · ‎11-28-2024

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

lbendlin · ‎11-28-2024

Access denied.

v-yaningy-msft · ‎11-28-2024

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

lbendlin · ‎11-27-2024

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

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

nicnic38 · ‎11-28-2024

post updated : file added

multi levels calculations