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.
Hi everyone,
I am having a doubt:
I created a Measure that works perfectly per each element in my table.
The problem appears when in Power BI, I select more than one element in my filter. The Measures sums the values of the elements I selected and then it makes the calculations. What I need is to calculate the Measure to each element in the filter and then to sum the final results.
Any opinion, Thanks
Hi @Vvelarde here goes the example:
Product Number | Product Category | Year | Date | Sales Amount | Units Sales |
A | CAT XX | 2016 | 31/01/2016 | 100.000 | 20.000 |
B | CAT XX | 2016 | 29/02/2016 | 50.000 | 25.000 |
C | CAT XX | 2016 | 31/03/2016 | 200.000 | 10.000 |
D | CAT XY | 2016 | 30/04/2016 | 10.000 | 600 |
E | CAT XY | 2016 | 31/05/2016 | 350.000 | 19.000 |
F | CAT XZ | 2016 | 30/06/2016 | 75.000 | 8.000 |
G | CAT XZ | 2016 | 31/07/2016 | 5.000 | 200 |
H | CAT XZ | 2016 | 31/08/2016 | 1.000.000 | 45.000 |
I | CAT ZZ | 2016 | 30/09/2016 | 150.000 | 20.000 |
A | CAT XX | 2015 | 31/01/2015 | 85.000 | 18.400 |
B | CAT XX | 2015 | 28/02/2015 | 42.500 | 23.000 |
C | CAT XX | 2015 | 31/03/2015 | 170.000 | 9.200 |
D | CAT XY | 2015 | 30/04/2015 | 8.500 | 552 |
E | CAT XY | 2015 | 31/05/2015 | 297.500 | 17.480 |
F | CAT XZ | 2015 | 30/06/2015 | 63.750 | 7.360 |
G | CAT XZ | 2015 | 31/07/2015 | 4.250 | 184 |
H | CAT XZ | 2015 | 31/08/2015 | 850.000 | 41.400 |
I | CAT ZZ | 2015 | 30/09/2015 | 127.500 | 18.400 |
We need to calculate the Price Growth versus Prior Year.
To obtain the final result we need to compare the Total Sales Current Year/ Total Deflated Sales.
The deflated sales measure is the result of: Units Sales Current year * Price/unit Prior Year (This calculation is perform for each product and the deflated is the sum of each results,)
Deflated Correct (individual) | |
A | 92.391,30 |
B | 46.195,65 |
C | 184.782,61 |
D | 9.239,13 |
E | 323.369,57 |
F | 69.293,48 |
G | 4.619,57 |
H | 923.913,04 |
Total deflated: | 1.653.804,35 |
THIS CALCULATION IS AT PRODUCT LEVEL, ,so, Total deflated sales is the sum of the result for each product.
The problem appears when we use filter for product category hierarchy, the formula first group the data for the product that belongs to a category and after that complete the calculation:
Total Sales Amount | 2016 | 1940000 |
Total Sales | 2015 | 1649000 |
Total units | 2016 | 147800 |
Total units | 2015 | 135976 |
Deflated Incorrect= 147800*(1649000/135976)=1792391
thanks!!
According to your description, you should use SUMX function which returns the sum of an expression evaluated for each row in a table to calculate the Total Sales instead of SUM function. The formula below is for your reference.
Total Sales = SUMX ( YourSalesTableName, YourSalesTableName[Sales Amount] * YourSalesTableName[Units Sales] )
Regards
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |