Is there an Easier Sub Total DAX Formula than this?

[JimMatthews]

Hi, I have a question with a sub total percentages problem.

I have done what I want and it is working as I want.

As you can see from this image, https://www.dropbox.com/s/9m3su0l2lcstvcr/subtotals.png

the 1, 2 and 3 KG sub totals add up to 100%. Also, when I drill into one of the pack sizes, the percentages add to 100% for that pack size - this is exactly what I want.

My question is, is there any 'easier' and 'better' DAX to get this result other than what I have created here.

[FONT=Calibri]=if(HASONEVALUE(Products[ProdDesc])[/FONT]
[FONT=Calibri]     ,DIVIDE([Total Sales][/FONT]
[FONT=Calibri]                  ,CALCULATE([Total Sales][/FONT]
[FONT=Calibri]                                       ,all(Products)[/FONT]
[FONT=Calibri]                                       ,VALUES(Products[Pack Size])[/FONT]
[FONT=Calibri]                                        )[/FONT]
[FONT=Calibri]                   )[/FONT]
[FONT=Calibri]     ,DIVIDE([Total Sales][/FONT]
[FONT=Calibri]                  ,CALCULATE([Total Sales][/FONT]
[FONT=Calibri]                                       ,all(Products)[/FONT]
[FONT=Calibri]                                       )[/FONT]
[FONT=Calibri]                  )[/FONT]
[FONT=Calibri]     )[/FONT]

Thanks

 

[scottsen]

Nope. There is no "generic" or one size fits all "Percent of Parent" in dax. If you don't need to USE those % in other calcs, you could just change the display settings on the pivot table column in excel, but for "real dax"... that is it.

 

[JimMatthews]

OK - thanks a lot. It just looked a bit complex, so I wondered if there was a better way. Thanks for confirming.

 

[marco.russo]

You might want to use ISFILTERED instead of HASONEVALUE (it's also faster).
A longer explanation is covered here: Clever Hierarchy Handling in DAX – SQLBI

 

[JimMatthews]

Thanks for the tip Marco. Funny - I am just in the middle of watching your video on xVelocity under the hood. I love your TV site!