cancel
Showing results for
Did you mean:
Helper I

## results of a measure use in a slicer

Hi folks

I need help in using results from a measure as a slicer.

Simple example, when the sales amount is higher than the year before the customer is a winner, else a loser.

I calculate the sales amount for year and last year (because I use yeartodate-function and in the report slicer for quarter and month).

I also calculate the sales difference with:

YTD U diff = salestable[YTD sales Y] - salestable[YTD sales PY]

Now I have a measure which split the customer into winner, neutral and loser.

Winner-Loser = IF( salestable[YTD U diff] < 0; "Loser"; IF( salestable[YTD U diff] = 0; "Neutral"; "Winner" )

- ( When I use this as a calculated column the results always show me the total of the year, that´s wrong for my case - or can I  filter this??? )

To use Winner and Loser as a slicer I created an additional table 'G&V' with a column [G&V] with text rows "Winner"; "Neutral" and "Loser". (also a column 'G&V'[Nr] with integer, 1 for "Winner", 0 for "Neutral" and 2 for "Loser".)

This column I will use as a slicer. My problem is how to "connect" the results of the Winner-Loser-Measure with the column [G&V].

I tried measures for winner and loser like…
winner =

IF( ISFILTERED( 'G&V'[G&V] ) ; 'Tabelle1'[YTD U diff] > 0 ; Tabelle1[YTD U diff])

Find an example for average (wrong for me) and try to modify, but also not the correct result.

Winner =

IF (

ISFILTERED ( 'G&V'[Nr] );

AVERAGEX(

FILTER ( 'Tabelle1'; 'Tabelle1'[YTD U diff] > VALUES ( 'G&V'[Nr] ) );

'salestable'[YTD U diff]

))

I think I have to use VALUES but I have no idea how !?

Jorg

actual result:

and when I use the slicer, I have not the expected result - I only want to see the winner.

1 ACCEPTED SOLUTION
Microsoft

@JWE,

Measure =
var selectedvalue = IF(HASONEFILTER(Slicer[Type]),LASTNONBLANK(Slicer[Type],0),BLANK())
return IF(HASONEFILTER(Slicer[Type]),IF(ISERROR(SEARCH(salestable[Winner-Loser],selectedvalue)),0,1),1)

And use this measure in your visual as a filter.

Regards,

Charlie Liao

2 REPLIES 2
Microsoft

@JWE,

Measure =
var selectedvalue = IF(HASONEFILTER(Slicer[Type]),LASTNONBLANK(Slicer[Type],0),BLANK())
return IF(HASONEFILTER(Slicer[Type]),IF(ISERROR(SEARCH(salestable[Winner-Loser],selectedvalue)),0,1),1)

And use this measure in your visual as a filter.

Regards,

Charlie Liao

Helper I

Hi Charlie

thanks a lot. I was able to reconstruct it with your sample.

I hope I can combine other measures in my page. I want to use it furthermore for new and old customers, I will try it with your example.

-- How can I COUNT the results of this measure, means when I have one more "winner", I want to use a pie chart for visuals the numbers of winner/loser/neutral.

Thanks for more help.

Cheers Jorg

Announcements