Calculate total of subgroup

[Anonymous]

Hi,

I have raw data with below format. Each station and each date can have a multiple parameter name but the Total Qty will be the same as it it a qty of testing unit on that station / day. 

I want to create total of each station  (Total(Station)). And it works.

However, if I put some filter and start using date slicer, the calculation is wrong.

 ==> filter to station 1&2 only : Still get correct Total Station

After adjust date slicer, the Total Station is wrong

here are all the calculation

How can I fix this?

[raimon · Accepted solution]

Hi @Anonymous, 

Create 2 measures as below, both measures are working with changes in date and station slicer. 

Station Total =

CALCULATE(SUM(sample1[Total Qty]),filter(ALLSELECTED(sample1),sample1[Station]=MAX(sample1[Station])))

Grand Total = CALCULATE(SUM(sample1[Total Qty]),ALLSELECTED(sample1))

[Anonymous]

I change the formula a bit to

Station Total = CALCULATE([Total Test],FILTER(ALLSELECTED(sample1),sample1[Station] = MAX(sample1[Station])))

Using total qty will be double count but this works.
Can you explain me a bit more on the sample1[Station] = Max(sample1[Station]) ? What does this mena ?

[Anonymous]

That doesn't work. By using "ALL" function, it doesn't take the date fitler into account.

[Shaurya]

Hi @Anonymous,

For Grand Total use:

Grand Total = CALCULATE([Total Test], ALL('Sample1'))

Works for you? Mark this post as a solution if it does!
Consider taking a look at my blog: Forecast Period - Previous Forecasts