Summation based on a measure

[ChavdarG]

Dear all,

I already spent too much time on this, so hope you can help. Seems like a pretty basic thing to do but I cannot.

I have 2 slicers: Region and Country. The combination of the 2 selections, can give me the Zone.

I can call out the zone via a measure:

ZONE SELECTION = LOOKUPVALUE('ALL CCs'[Zone],'ALL CCs'[Country name],SELECTEDVALUE('ALL CCs'[Country name]),

'ALL CCs'[Region],SELECTEDVALUE('ALL CCs'[Region]))

Now I need to use the zone, which is returned here and sum up the column showing Gross Sales Revenue.

However, CALCULATE does not work with measures:

BP GSR 4 ZONE SELECTED = CALCULATE(SUM('ALL CCs'[GSR BP]),'ALL CCs'[Zone]=[ZONE SELECTION]))

"A function 'SELECTEDVALUE' has been used in a True/False expression that is used as a table filter

expression. This is not allowed."

What is a workaround to this?

Thank you,

Chavdar

[v-easonf-msft · Accepted solution]

Hi , @ChavdarG 

Are you referring to an error like the following?

If so ,it is suggested to  set a variable to store the value of the measure.

Please change you measure as below:

BP GSR 4 ZONE SELECTED = 
VAR a = [ZONE SELECTION]
RETURN
    CALCULATE ( SUM ( 'ALL CCs'[GSR BP] ), 'ALL CCs'[Zone] = a )

You can also use the filter function to encapsulate your filter expression:

BP GSR 4 ZONE SELECTED2 =
CALCULATE (
    SUM ( 'ALL CCs'[GSR BP] ),
    FILTER ( 'ALL CCs' , 'ALL CCs'[Zone] = [ZONE SELECTION] )
)

Best Regards,
Community Support Team _ Eason

[ChavdarG]

Hello,

Yes, this is it. I knew I was missing something and it shouldn't be too complicated.

Thx a lot!

[edhans]

Try one of the following:

VAR varSelection =
    LOOKUPVALUE(
        'ALL CCs'[Zone],
        'ALL CCs'[Country name], SELECTEDVALUE( 'ALL CCs'[Country name] ),
        'ALL CCs'[Region], SELECTEDVALUE( 'ALL CCs'[Region] )
    )
RETURN
    CALCULATE(
        SUM( 'ALL CCs'[GSR BP] ),
        'ALL CCs'[Zone] = varSelection
    )

That still uses a predicate, which may be the issue, not that CALCULATE cannot work with measures. If that fails, try this:

Final Measure =
VAR varSelection =
    LOOKUPVALUE(
        'ALL CCs'[Zone],
        'ALL CCs'[Country name], SELECTEDVALUE( 'ALL CCs'[Country name] ),
        'ALL CCs'[Region], SELECTEDVALUE( 'ALL CCs'[Region] )
    )
RETURN
    CALCULATE(
        SUM( 'ALL CCs'[GSR BP] ),
        FILTER(
            ALL( 'ALL CCs'[Zone] ),
            'ALL CCs'[Zone] = varSelection
        )
    )

It replaces the predicate with the underlying code using FILTER.

[parry2k]

@ChavdarG It will be easier if you share pbix file using one drive/google drive with the expected output. Remove any sensitive information before sharing.