cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

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?

1 ACCEPTED SOLUTION
Community Support

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 4
Community Support

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.

Microsoft Employee

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

Regards,

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Community Champion

try

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

Helper III

@pranit828  Thanks but that does not accomplish the desired result

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors