Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
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
still calculation 2 ans calculation 3 to do 😞
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
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?
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
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.
the data file is updated
still access denied - please check the sharing settings.
link updated
thanks
here is the dataset : https://drive.google.com/file/d/1DS44dI23jA_YRSB_JqXSiGyqa4J5HmUW/view?usp=drive_link
Access denied.
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
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...
post updated : file added
User | Count |
---|---|
12 | |
12 | |
8 | |
8 | |
6 |
User | Count |
---|---|
27 | |
19 | |
13 | |
11 | |
7 |