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 Guys,
I have the following table:
Product | Category | Sales Values |
A | Red | 2 |
A | Red | 2 |
B | Red | 3 |
B | Red | 3 |
B | Red | 2 |
C | Blue | 2 |
C | Blue | 2 |
C | Blue | 2 |
C | Blue | 2 |
I want to create a measure/column/visualization of some sort that calculates the sum of the sales values for a specific product and divide it by the sum of all sales values for the category that this product is in. Also I want to add a card that shows the result when i am filtering for a specific product without messing up my other data.
For example I want to calculate the share of Product A in Category Red: (2+2)/(2+2+3+3)*100.
I also want to do this type of calculation for other Products. Do I have to do it manually or is there some kind of way to make a function in the visualization pane where i can choose whatever product i want and it calculates it for me?
I tried sometheing like Share = CALCULATE(Sum(('Table'[Sales Value])), FILTER('Table', 'Table'[Product]="Red"))
Solved! Go to Solution.
@Anonymous I have created a small PBIX file using your sample data. It is giving me correct values. Maybe if you can share the complete data or Model, I can give a further try. PBIX File
@Omega What if i add a slicer and want my data to filter as the selection of the slicer?
In that case, you need to use allselected() function in your formula which reads the selected value.
How would it fit in the formula that you gave me?
You will change allexcept with allselected
It doesn't work because in this case it divides by itself and I always get 100%.
As I understand, you will be using a Slicer on Product. Give below a try and let me know.
Measure 15 = var Category = CALCULATE(sum(Table1[Sales Values]),filter(all(Table1),Table1[Category] in VALUES(Table1[Category]))) return DIVIDE(sum(Table1[Sales Values]),Category,0) * 100
@Anonymous I have created a small PBIX file using your sample data. It is giving me correct values. Maybe if you can share the complete data or Model, I can give a further try. PBIX File
Try creating the below measure:
Measure = var Category = CALCULATE(SUM(Table1[Sales Values]),ALLEXCEPT(Table1,Table1[Category])) return DIVIDE(SUM(Table1[Sales Values]),Category,0)*100
@Omega When I use it it returns a very close result but lower than expected in every case. Since the file is too large I can't manually where the error comes from. Maybe it doesn't take all the data I want, or it just divides by a larger amount. How can I make sure that even if there are duplicated products I still get the correct result?
Maybe it's due to rounding or hidden decimals? Double check the dataset 😉
Hi @Anonymous,
you've a many-to-many relationship between products and categories, is that really the case? Can one product really belong to different categories?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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 | |
101 | |
86 | |
64 |