PowerPivot DAX Formula to calculate Grand Total

[datiduke]

Hi,
I am looking for a formula to give me the GrandTotal of the displayed values and all the applied filters. So far I have tried the two following formulas:
CALCULATE(SUM(Products[NetSales]),ALLSELECTED(Products))
That does give me the Grand Total, but only if I have one column and no other filters applied. If I have e.g. two FinYears in the columns, the value given is the sum of total 2013 and 2014, but I want the total displayed (and calculated further) with the total of the selected year.

I did also try ALLEXCEPT, which would give me the total in each row for each column, but if I use inside filters (like excluding some categories) then the value is not correct anymore.

The picture attached is what I want as Total Sales the same value in each row.

Anyone an idea?
Cheers,
Andy

 

[ashish mehra]

Hi Andy,

I have given a shot to your above query.

To view the report Yearly & Total Sales at the same point of time, you need to follow the below steps:

1) Click on Analyze Ribbon




2) From Calculations group select Fields, Items, & Sets option

3) Click on Create Set Based on Column Items

In my data I have taken Sales for Year 2010 to 2014, you need to select years as per your requirement.




Please see the attached file for your reference & update if it helps.

Regards,
AM

 

[datiduke]

hm. I dont see yet how this helps with the inital question. How do I use the Grand Total in further measures? Lets say I want to devide the Product1 sales in 2013 with the grand total. I know I could display the values as % of grand total, but I really need the Grand-Total-Value as a calculated field.

I believe Microsoft hasnt programmed that function yet. I have been looking through many forums and did not find any satisfying answer.

Cheers, Andy

 

[ashish mehra]

Hi Andy,

I would appreciate if you can post a sample file with the desired result.
Although, I have tried on a similar requirement.

Column D is the calculated column which will be there (fixed) even if you select multiple or all categories at a time.







Regards,
AM

 

[datiduke]

Hi,
here is the example again from my first post.

Total Sales (for each year different) should change as well whatever filter I use. (if I select Product 1 & 2 only, TotalSales should be 300 everywhere).

I need the TotalSales as a calculated field. The next step I want to do is to devide the sales/totalSales, so I will get a %. I know I can get get a % with "view values as...", but I need it as a measure, a "real number".

Thanks.
Andy