Forum Discussion

Mainer04401's avatar
Mainer04401
Icon for Helper III rankHelper III
5 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 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?

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.

4 Replies

  • 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Mainer04401 

    try 

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

    • Mainer04401's avatar
      Mainer04401
      Icon for Helper III rankHelper III
      5 years ago

      Anonymous  Thanks but that does not accomplish the desired result

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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