Sub totals with Multiple Columns

[SamTaylor]

Hi,

Please can you help.  Below is a picture of the subtotal FP Sales % I am trying to achieve.

It works when I only have Rep_Grp and Rep_SC in the pivot table. But when I add Prod_Rep, Rep_Desc and RRP it does not work.

I am using:

FP Sales % = [Total FP Sales] / CALCULATE ( [Total FP Sales] , All ( Products [Rep_Grp]))

Thanks

Sam

[v-stephen-msft]

Hi @SamTaylor ,

Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,
Stephen Tao

[v-stephen-msft]

Hi @SamTaylor ,

For the issue of subtotal not working properly, HASONEVALUE function might be the solution

There is a similar post, please kindly refer to:

Subtotal not working properly

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@SamTaylor , Not very clear. You can Try

FP Sales % = [Total FP Sales] / CALCULATE ( [Total FP Sales] , All ( Products ))

You can also use allexcept as per need

Change the column as per need. This means to remove all filter except Rep_Grp

FP Sales % = [Total FP Sales] / CALCULATE ( [Total FP Sales] , allexcept (Products ,  Products [Rep_Grp]))

https://www.sqlbi.com/articles/managing-all-functions-in-dax-all-allselected-allnoblankrow-allexcept...

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/

[AllisonKennedy]

Hi @SamTaylor 

What result are you looking for? What do you mean when you say it works with only Rep_Grp and Rep_SC in the pivot table but then breaks? Are  Prod_Rep, Rep_Desc and RRP all in the same table as Rep_Grp and Rep_SC? Please provide data model picture too (diagram view). 

It also looks like you have some filters which may be further complicating things? 

The measure you have written will only clear filters on the Rep_Grp column, so it keeps filters from other columns such as Prod_Rep and Rep_Desc. If you want the percent of Grand Total, in Power BI you could just use ALL() but I don't think you have that option in Excel. So you need to determine what filters you want to remove. I can't remember if you can use ALL(Product) in Excel - that might get you close. 

https://docs.microsoft.com/en-us/dax/all-function-dax 

If this doesn't help, please describe what values you want as grand total and what filters you have applied, as well as your data model relationships. 

Cheers!