Forum Discussion

Mainer04401's avatar
Mainer04401
Icon for Helper III rankHelper III
6 years ago
Solved

Allselected in Matrix Drilldown

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 drill...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
5 years ago

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.