Allselected in Matrix Drilldown

[Mainer04401]

I created a measure for % of sales with the following measure:

Sales Vol Contrib = divide([Total_Vol],calculate([Total_Vol],allselected(StoreList)),0)

It works, but in a matrix with a drilldown on the rows, is there a way for the denominator to only include the parent row?

See the below screen shot for an example.  [County, State] is the parent rows of [City, State] in the StoreList table ...

In the example of Portland, ME, how can I make sales vol contribution be 40.5% (1,690 / 4,174) instead of 11% (1,690 / 14,867) as shown, without affecting the parent rows which show the county's contribution to the state?

[v-jingzhang · Accepted solution]

Hi @Mainer04401 , I used some simple data to test and get what you required. Please try below method.

Sample data 'Sales':

Create measures:

Total Sales = SUM(Sales[Total])

Contribution 2 = DIVIDE([Total Sales],CALCULATE([Total Sales],ALLSELECTED(Sales)),0)

Contribution 3 = DIVIDE([Total Sales],CALCULATE([Total Sales],ALLEXCEPT(Sales,Sales[Country, State])),0)

Final Contribution = IF(ISFILTERED(Sales[City, State]),[Contribution 3],[Contribution 2])

 Put Final Contribution in the matrix and see the result:

Reference for ISFILTERED: https://docs.microsoft.com/en-us/dax/ISFILTERED-function-dax

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.

[mahoneypat]

Just add the City, State column to your ALLSELECTED as below.

Sales Vol Contrib = divide([Total_Vol],calculate([Total_Vol],allselected(StoreList[City, State])),0)

Regards,

Pat

[Anonymous]

Hi @Mainer04401 

try 

Sales Vol Contrib = divide([Total_Vol],calculate(sum([Total_Vol]),allexcept('table','table'[parent1],'table'[parent2])),0)

[Mainer04401]

@Anonymous  Thanks but that does not accomplish the desired result