Measure for matrix visual that rolls up values from child rows to parent rows.

AndyB87 — Thu, 15 Jun 2023 17:21:42 GMT

I'm trying to write a measure for a matrix visualization that will roll up a calculation of a percentage from child rows to parent rows.

I have a data model with a fact table called 'retailSales' that contains transaction data and a dimension table called 'product' that contains product information. Our products are divided into a taxonomy of categories, so the product table has fields for [masterCategory], [category], and [subcategory]. I'm putting together an analysis of our product categories. I have a matrix where the rows drill down from master category to category to subcategory. I have a column that calculates dollar sales for each row. I'd like to write a measure that calculates each row's dollar sales value as a % of its parent row subtotal (and % of total sales for each master category). It should look like the following example:

So far, I have a measure that successfully calculates each row's % share of total sales, but it doesn't roll up percentages from child rows to parent rows the way I want. It calculates a child row as a % of the total rather than its parent. It looks like this:

CategoryShare = DIVIDE( SUM( retailSales[soldPrice] ), CALCULATE( SUM( retailSales[soldPrice] ), ALLSELECTED( product ) ) )

Any thoughts on how to write a measure that does what I'm looking for?

Re: Measure for matrix visual that rolls up values from child rows to parent rows.

some_bih — Fri, 16 Jun 2023 05:58:36 GMT

Hi AndyB87 try to use ALLEXCEPT for your column (I guess [category],) as shown in example https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/ I hope this help

topic Re: Measure for matrix visual that rolls up values from child rows to parent rows. in DAX Commands and Tips

Measure for matrix visual that rolls up values from child rows to parent rows.

Re: Measure for matrix visual that rolls up values from child rows to parent rows.