Percentage of subtotals in a matrix

[dorukturkoglu]

Hi all,

I have a measure which I am calculating it over a period of months and channel, and under each channel i have another variable. I use the matrix visual, and for the measure i choose show it as column total. However when I dig one level, i see that percentages are based on grand column total, not subtotal

I would like the sub types to add up to %100 for each channel, is that doable?

Please note that the value i am showing in matrix is a measure, not a column

Thanks

[tharunkumarRTK · Accepted solution]

@dorukturkoglu 

There are many ways to achieve this, here is one 

[Measure] = 
Var __ChannelSales = calculate([Total], allselected(Table[Program]))
Var result = if( isinscope(Table[Program]), divide([Measure], __ChannelSales), 1  )

Return result

Here is one example:

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[dorukturkoglu]

Hi,

Thanks a lot for your prompt response!

It works however I would need to add the new metric as a separate 'value', and i have multiple months as columns, so it makes the table cluttered.

Is it possible to have achieve this with a single metric? I want to see the channel contribution as lets say %5, and want to see the breakdown by %40 and %60 when I click to the plus sign.

[tharunkumarRTK]

@dorukturkoglu 

I would suggest you to checkout this article. You should be able to build the logic as per your requirement 

https://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/