How to modify my expression using Var , so that i can insert YTD , LYTD , GP%

[admin11]

Hi All

I have below expression working fine , it will return total sales amount :-

_amount_select3 =

SWITCH(

SELECTEDVALUE(SliceTable[Slice]),

"BRAND_C",IF(ISINSCOPE(summrizetable2[rank]),

CALCULATE(SUM('SALES'[sales]),FILTER(ALLSELECTED('SALES'),[BRAND_C]=MAX('summrizeTable1'[BRAND_C]))), SUM('SALES'[sales])))

May i know how to modify the above expression using Var like below :-

_amount_select3 =

VAR1=SUM('SALES'[sales]

SWITCH(

SELECTEDVALUE(SliceTable[Slice]),

"BRAND_C",IF(ISINSCOPE(summrizetable2[rank]),

CALCULATE(VAR1),FILTER(ALLSELECTED('SALES'),[BRAND_C]=MAX('summrizeTable1'[BRAND_C]))), VAR1)))

 Hope some one can help me advies me on above expression.

Paul

[Samarth_18 · Accepted solution]

Hi @admin11 ,

It would be like this:-

_amount_select3 =
VAR VAR1 =
    SUM ( 'SALES'[sales] )
RETURN
    SWITCH (
        SELECTEDVALUE ( SliceTable[Slice] ),
        "BRAND_C",
            IF (
                ISINSCOPE ( summrizetable2[rank] ),
                CALCULATE ( VAR1 ),
                FILTER (
                    ALLSELECTED ( 'SALES' ),
                    [BRAND_C] = MAX ( 'summrizeTable1'[BRAND_C] )
                )
            ),
        VAR1
    )

Thanks,

Samarth

[admin11]

@Samarth_18 

i get error msg.

[Samarth_18]

@admin11 ,Please try this:-

_amount_select3 =
SWITCH (
    SELECTEDVALUE ( SliceTable[Slice] ),
    "BRAND_C",
        IF (
            ISINSCOPE ( summrizetable2[rank] ),
            CALCULATE (
                VAR1,
                FILTER (
                    ALLSELECTED ( 'SALES' ),
                    [BRAND_C] = MAX ( 'summrizeTable1'[BRAND_C] )
                )
            ),
            VAR1
        )
)

[admin11]

@Samarth_18 

i Just notice that all row return same value. 

Hope you can advise me where go wrong ?

Paul

[Samarth_18]

Small correction. Please try this:-

_amount_select3 =
VAR VAR1 =
    SUM ( 'SALES'[sales] )
RETURN
    SWITCH (
        SELECTEDVALUE ( SliceTable[Slice] ),
        "BRAND_C",
            IF (
                ISINSCOPE ( summrizetable2[rank] ),
                CALCULATE (
                    VAR1,
                    FILTER (
                        ALLSELECTED ( 'SALES' ),
                        [BRAND_C] = MAX ( 'summrizeTable1'[BRAND_C] )
                    )
                )
            ),
        VAR1
    )

[admin11]

@Samarth_18 

Thank you for your help , but still the same.

[Samarth_18]

@admin11 is it possible for you to share your PBIX file after removing sensitive data since its bit difficult to answer without seeing data. 

Is it working fine without variable?

[admin11]

@Samarth_18 

My PBI sample file

https://www.dropbox.com/s/t9daa48alz2t5fy/dynamic%20diamension%20Small%20file%20size%20ver010.pbix?d...

Paul

[admin11]

@Samarth_18 

wow you are very good . thank you very much.