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

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

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

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

multi levels calculations | Microsoft Fabric Community

15 Replies

lbendlin
Super User
1 year ago
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
- nicnic38
  Frequent Visitor
  1 year ago
  post updated : file added

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

multi levels calculations.pbix23 KB

nicnic38
Frequent Visitor
1 year ago
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
  Super User
  1 year ago
  Please provide sample data that fully covers your issue.
  Please show the expected outcome based on the sample data you provided.
  - nicnic38
    Frequent Visitor
    1 year ago
    here is the dataset : https://drive.google.com/file/d/1DS44dI23jA_YRSB_JqXSiGyqa4J5HmUW/view?usp=drive_link
nicnic38
Frequent Visitor
1 year ago
https://drive.google.com/file/d/1DS44dI23jA_YRSB_JqXSiGyqa4J5HmUW/view?usp=sharing
- lbendlin
  Super User
  1 year ago
  
  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
    Frequent Visitor
    1 year ago
    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
Frequent Visitor
1 year ago
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
Frequent Visitor
1 year ago
still calculation 2 ans calculation 3 to do 😞

Forum Discussion

multi levels calculations

15 Replies

Related content

Multi level drill-trough

Multi level hierarchy in Gantt chart by microsoft corporation

Multi-level JSON column

Multi Level Column Headers

Multi-Record Calculation Issue