Show correct subtotals & totals for Measures

[ReneDH]

All,

I've searched the forum already and find some solutions on how people are trying to solve this, however I can't figure out how to get this working.

I have a mastertable which looks like this:

Location	Productgroup	Attribute	Value
A	1	Costs	100
A	1	Volume	10
A	2	Costs	200
A	2	Volume	10
B	1	Costs	500
B	1	Volume	10
B	2	Costs	1000
B	2	Volume	10

And for every location I need to calculate the costs that we are making with the volume.
This is not a difficult thing to do, however there is a exception that for location A i need to consider all costs and volumes, whilst for other location i need to only take product group 1.

Based on this example I created the following measure.

Costs per Volume = 
VAR LOCATION =
    SELECTEDVALUE (Data[Location] )
VAR COSTSVOLUME_all =
    DIVIDE ( [COSTS], ( [VOLUME] ), BLANK () ) 
VAR COSTSVOLUME_GROUP1 =
    DIVIDE (
        CALCULATE (
            [COSTS],
            Data[ProductGroup] = 1
        ),
        CALCULATE ( [VOLUME], Data[ProductGroup] = 1 ),
        BLANK ()
    ) 
RETURN
    SWITCH (
        TRUE (),
        LOCATION = "A", IF( COSTSVOLUME_all = 0, BLANK (), COSTSVOLUME_all),
               IF ( COSTSVOLUME_GROUP1 = 0, BLANK (), COSTSVOLUME_GROUP1 )
    )

However when I add the cluster in front of my table (so both locations have the same cluster) it will calculate the subtotal for the cluster based on the formula for COSTVOLUME_GROUP1 whilst it would need to take into account the exceptions for location A.

[DataInsights]

@ReneDH,

Try this measure. The concept is that it creates a virtual table in the current filter context, calculates your measure for each row in the virtual table, and then iterates the virtual table using SUMX.

CPV Total = 
VAR vTable =
    ADDCOLUMNS (
        SUMMARIZE ( Data, Data[Location], Data[Productgroup] ),
        "@Amount", [Costs per Volume]
    )
VAR vResult =
    SUMX ( vTable, [@Amount] )
RETURN
    vResult

Notice the difference in totals between the two measures:

[ReneDH]

Thank you for the reply, it is not exactly what I meant.

The formula that I posted is correctly calculating the cost per volume, per location.

However my total should be taken into account the exception that I wrote in my first formula. So calculate  with 800 on costs for the total and 30 on volume would result in an cost per volume of 26.67 

Hope this clarifies what I try to achieve, but already many thanks for looking into this topic.

[DataInsights]

@ReneDH,

Is this the desired result (the first two rows have Costs per Volume of 26.67)?

[ReneDH]

The idea is as follows:

So the cost per volume is calculated per location.

Whilst it needs to be also calculated in total (including the rules as stated in the first formula).

It is quite difficult to explain without giving insight into the real data behind, but due to the data i cant disclose more information.

[DataInsights · Accepted solution]

@ReneDH,

Try this measure:

Costs per Volume = 
VAR LOCATION =
    SELECTEDVALUE ( Data[Location] )
VAR COSTSVOLUME_all =
    DIVIDE ( [COSTS], [VOLUME] )
VAR COSTSVOLUME_GROUP1 =
    DIVIDE (
        CALCULATE ( [COSTS], Data[ProductGroup] = 1 ),
        CALCULATE ( [VOLUME], Data[ProductGroup] = 1 )
    )
VAR vResultSingle =
    SWITCH (
        TRUE (),
        LOCATION = "A", IF ( COSTSVOLUME_all = 0, BLANK (), COSTSVOLUME_all ),
        IF ( COSTSVOLUME_GROUP1 = 0, BLANK (), COSTSVOLUME_GROUP1 )
    )
VAR vNumeratorTotal =
    CALCULATE ( [COSTS], Data[Location] = "A" || Data[Productgroup] = 1 )
VAR vDenominatorTotal =
    CALCULATE ( [VOLUME], Data[Location] = "A" || Data[Productgroup] = 1 )
VAR vResultTotal =
    DIVIDE ( vNumeratorTotal, vDenominatorTotal )
VAR vResult =
    IF ( HASONEVALUE ( Data[Location] ), vResultSingle, vResultTotal )
RETURN
    vResult

[ReneDH]

Great this is the solution, thank you very much for the support.

One thing to ask:
The vNumeratorTotal and vDenominatorTotal calculation does that handle the filters in sequence? Because it calculates the costs if it location A or productgroup 1. 
However Location A has also product group 1.

Am I right that the calculate function first looks at the first argument (so if it is location A, it just calculates the costs over the whole location), whilst it is not location 1 it calculates the costs over the product group?

[DataInsights]

@ReneDH,

The filter in the CALCULATE function uses OR ("||"), so if Location = A or ProductGroup = 1, that row is included.