Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Good morning, Power BI gurus. I humbly present the problem that has stumped me for the last 24 hours of screen time (I wish I were kidding):
I am working with sales data that has a significant amount of duplication in it. The duplication was done deliberately, as it was neccessary for another calculation I had to make.
The tasks are to find both the sum of the distinct product's sale priceover a given time period, and the percent of the overall grand total each distinct product's sale price is. The data also has a date component to it, as the final report will need to be sliced by time period.
Here is some example data:
Year | Product | Sale Price |
2022 | A | 100 |
2022 | A | 100 |
2022 | A | 100 |
2022 | A | 100 |
2022 | A | 100 |
2022 | B | 150 |
2022 | B | 150 |
2022 | C | 50 |
2022 | C | 50 |
2022 | C | 50 |
2022 | C | 50 |
2022 | D | 200 |
2022 | D | 200 |
2022 | D | 200 |
2022 | D | 200 |
2022 | D | 200 |
The final report with the above aggregations is supposed to look like this:
Year | Product | Sale Price | Percent |
2022 | A | 100 | 20% |
2022 | B | 150 | 30% |
2022 | C | 50 | 10% |
2022 | D | 200 | 40% |
Total | 500 | 100% |
I was able to create a measure that accurately calculates the sale price and the distinct sum correctly using SUMX and Calculate (I'm still learning about context transitions, and I know that's what's happening here):
SUMX(
VALUES('Data'[Product]),
CALCULATE(
MAX('Data'[Sale Price]),
)
)
However, I am unable to figure out how to accurately calculate the percent column. Intuitively it's simple: just take the sale price of each displayed value and divide it by the displayed grand total. However I cannot figure it out.
Any help or guidance here would be much appreciated.
Solved! Go to Solution.
This video covers your requirement :
This video covers your requirement :
@Greg_Deckler Thank you very much for your reply. I tested the solution out, but unfortionatly that it didn't work. Your code returns 100% for each product, and after I debugging I discovered that the "__Total" variable is actually recalculating the exact value my original code did for the sale price, and 100/100 will always be %100.
Any other ideas? 😅
@DK_analyst Here is one way:
Price Percent =
VAR __Row = AVERAGE(Data[Sale Price])
VAR __Table = SUMMARIZE(ALL('Data'),[Year],[Product],"__Avg",AVERAGE(Data[Sale Price]))
VAR __Total = SUMX(__Table,[__Avg])
VAR __Result =
IF(
ISINSCOPE('Data'[Product]),
DIVIDE(__Row, __Total),
DIVIDE(__Total, __Total)
)
RETURN
__Result
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |