Dynamic Measure Problem By Switch Statement

[snandy2011]

Hi all,

I just want to do dynamic calculation of one of my measure. Let me please explain,

I have a slicer of 4 different Channel group, like, Direct,Social, referral and Organic.

When I will click  suppose direct and social, then it will show Percentage of leading of those two selected channel group.

After, when iclick Organic and referral, it will show percentage of leading between organic and referral.

I have calculated on prcentage of leading on other's measure.like

Direct vs  Social = DIVIDE(  ([Current Month Session For Direct TC] - [Current Month Session For Social TC]), [Current Month Session For Social TC] )

Similar I have Organic vs Referral and Others.

for making this dynamic i have written following formula,

Measure Selection for TC = IF(ISCROSSFILTERED( 'Table For Comparison'[Default Channel Grouping]),
SWITCH( TRUE(),
	AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Direct",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Organic Search"),[Direct vs  Organic],
    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Direct",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Referral"),[Direct vs  Referral],
    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Direct",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Social"),[Direct vs  Social],
    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Organic Search",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Referral"),[Organic vs Referral],
    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Organic Search",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Social"),[Organic vs  Social],
    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Referral",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Social"),[Referral vs  Social],
       
	BLANK()),BLANK() ) 

but, it shows me, " A table of multiple values was supplied where a single value was expected"

can i use multiple values statement in switch, if not, then what can be the others solution?

Desired output,

Supppose direct's current month session = 500, organic =200,Social=600, Referral =100

if i select direct and organic both on the slicer it will show (500-200/200), again when i select social and referral it will show, (600-100/100),

And, these calculation will be on one measure (dynamic)

Please help to solve this problem

Any suggesation is really appreciable.

Thanks,

snandy2011 

[v-danhe-msft · Accepted solution]

Hi @snandy2011,

Based on my test, you could refer to below steps:

1.Sample data:

2.Create a new table and a measure.

New table:

Copy Table = SUMMARIZE('Table1','Table1'[Channel group],'Table1'[Value])

New measure:

Measure = var a=CALCULATE(SUM(Table1[Value]))
    var b=CALCULATE(SUM('Copy Table'[Value]))
    return DIVIDE(ABS(a-b),MIN(a,b))

Now you could use the different slicers to show the dynamic calculation.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/jik5izp2fyrxvs9/Dynamic%20Measure%20Problem%20By%20Switch%20Statement.pbix...

Regards,

Daniel He

[snandy2011]

Hi @v-danhe-msft

Thanks a lot for your solution.I have tried your solution and It has worked perfect. I have achieved it by Dax   also. But your soluttion is so simple. got a new thing to learn

Thanks once again for your awesome reply.

Thanks,

snandy2011

[snandy2011]

Hi all,

I just want to do dynamic calculation of one of my measure. Let me please explain,

I have a slicer of 4 different Channel group, like, Direct,Social, referral and Organic.

When I will click  suppose direct and social, then it will show Percentage of leading of those two selected channel group.

After, when iclick Organic and referral, it will show percentage of leading between organic and referral.

I have calculated on prcentage of leading on other's measure.like

Direct vs  Social = DIVIDE(  ([Current Month Session For Direct TC] - [Current Month Session For Social TC]), [Current Month Session For Social TC] )

Similar I have Organic vs Referral and Others.

for making this dynamic i have written following formula,

Measure Selection for TC = IF(ISCROSSFILTERED( 'Table For Comparison'[Default Channel Grouping]),

SWITCH( TRUE(),

       AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Direct",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Organic Search"),[Direct vs  Organic],

    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Direct",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Referral"),[Direct vs  Referral],

    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Direct",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Social"),[Direct vs  Social],

    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Organic Search",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Referral"),[Organic vs Referral],

    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Organic Search",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Social"),[Organic vs  Social],

    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Referral",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Social"),[Referral vs  Social],

       BLANK()),BLANK() )

but, it shows me, " A table of multiple values was supplied where a single value was expected"

can i use multiple values statement in switch, if not, then what can be the others solution?

Desired output,

Supppose direct's current month session = 500, organic =200,Social=600, Referral =100

if i select direct and organic both on the slicer it will show (500-200/200), again when i select social and referral it will show, (600-100/100),

And, these calculation will be on one measure (dynamic)

Please help to solve this problem

Any suggesation is really appreciable.

Thanks,

snandy2011

[snandy2011]

Hi all,

I just want to do dynamic calculation of one of my measure. Let me please explain,

I have a slicer of 4 different Channel group, like, Direct,Social, referral and Organic.

When I will click  suppose direct and social, then it will show Percentage of leading of those two selected channel group.

After, when iclick Organic and referral, it will show percentage of leading between organic and referral.

I have calculated on prcentage of leading on other's measure.like

Direct vs  Social = DIVIDE(  ([Current Month Session For Direct TC] - [Current Month Session For Social TC]), [Current Month Session For Social TC] )

Similar I have Organic vs Referral and Others.

for making this dynamic i have written following formula,

Measure Selection for TC = IF(ISCROSSFILTERED( 'Table For Comparison'[Default Channel Grouping]),

SWITCH( TRUE(),

       AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Direct",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Organic Search"),[Direct vs  Organic],

    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Direct",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Referral"),[Direct vs  Referral],

    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Direct",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Social"),[Direct vs  Social],

    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Organic Search",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Referral"),[Organic vs Referral],

    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Organic Search",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Social"),[Organic vs  Social],

    AND(VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Referral",VALUES('Table For Comparison'[Default Channel Grouping]  ) = "Social"),[Referral vs  Social],

       BLANK()),BLANK() )

but, it shows me, " A table of multiple values was supplied where a single value was expected"

can i use multiple values statement in switch, if not, then what can be the others solution?

Desired output,

Supppose direct's current month session = 500, organic =200,Social=600, Referral =100

if i select direct and organic both on the slicer it will show (500-200/200), again when i select social and referral it will show, (600-100/100),

And, these calculation will be on one measure (dynamic)

Please help to solve this problem

Any suggesation is really appreciable.

Thanks,

snandy2011