Sales shares by category

[mbegg]

Hi, I have [Sales] by [Category] {A, B, C, D, E}

I want to calculate sales shares such as the below:

1. (A+B) / (A+B+C+D+E)
2. (A) / (A+B+C)

How do I set up Slicers & measures to allow a User to select which categories appear in numerator and denominator? 

The following code will work for 1 but not for 2

CALCULATE( SUM( [Sales]) , ALLSELECTED( [CATEGORY] )) / CALCULATE( SUM( [Sales]) , ALL( [CATEGORY] )) 

[v-sihou-msft]

@mbegg

In this scenario, you can't give a dynamic context for both numerator and denominator. For your requirement, I suggest you duplicate your Category column. Then you can use ALLSELECTED for different Category columns.

CALCULATE( SUM( [Sales]) , ALLSELECTED( [CATEGORY1] )) / CALCULATE( SUM( [Sales]) , ALLSELECTED( [CATEGORY2] )) 

Regards,

[mbegg]

@v-sihou-msft

Thanks for the reply, however it doesn't work for me...if [Category2] is just a duplicate of [Category1] then as soon as I apply a slicer  or filter to [Category1], [Category2] is automatically filtered to the exact same list of categories.

It seems like I need to duplicate the data set, so I can calculate the numerator and denominator using independent filters...

[mbegg · Accepted solution]

I thought I would post my fix as the solution. Others may have a different fix but the below worked for me. 

1. I duplicated the data set.
2. In Data1, I relabelled [Category] as [Category1] & likewise [Category2] in Data2. Same for [Sales]
3. I created a separate table with one column - [Category] {A, B, C, D, E}
4. I created a relationship between [Category], [Category1] & [Category2]
5. I created the below measure
   • CALCULATE( SUM( [Sales1]) , ALLSELECTED( [CATEGORY1] )) / CALCULATE( SUM( [Sales2]) , ALLSELECTED( [CATEGORY2] )) 
6. I included a filter on [Category] & [Category1].
   • E.g. if [Category] was filtered to {A,B,C,D,E} & [Category1] was filtered to {A,B}, the measure would calculate #1 from my original query. 
   • Then filter [Category] to {A, B, C} & [Category1] to {A,B} to calculate #2 from my original query.