Forum Discussion
Dax Product Measure for a Specific Name
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"))
14 Replies
- LivioLanzo
Solution Sage
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?
LivioLanzo No, 1 product belongs to 1 category
OmegaImpactful Individual
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 It seems good but Category is a calculated column and doesn't work.
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?
- Omega
Maybe it's due to rounding or hidden decimals? Double check the dataset ;)
AnkitBIAs 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
Omega What if i add a slicer and want my data to filter as the selection of the slicer?
- Omega
In that case, you need to use allselected() function in your formula which reads the selected value.
