topic choose the right filter context in measure to compare to wider category in DAX Commands and Tips

choose the right filter context in measure to compare to wider category

Anonymous — Mon, 15 Nov 2021 14:09:33 GMT

Hi,

I have the following data:

Sale ID	Sales Rep	Location	Success
1	Daniel	US	Yes
2	Michael	Canada	Yes
3	Yolanda	US	Yes
4	Yolanda	US	Yes
5	Lily	Europe	Yes
6	Lily	Europe	Yes
7	Michael	Canada	Yes
8	Michael	Canada	Yes
9	Michael	Canada	Yes
10	Lily	Europe	Yes
11	Daniel	US	Yes
12	Michael	Canada	No
13	Yolanda	US	No
14	Yolanda	US	No
15	Daniel	US	No
16	Lily	Europe	No
17	Lily	Europe	No
18	Daniel	US	No
19	Ann	Europe	No
20	Ann	Europe	No
21	Ann	Europe	Yes

I want to show in one visualization the success rate of the rep compared to the success rate in the rep's location and separately compared to all.

The idea is that the user will filter a specific rep and will get the following chart:

I added the following calculated measures:

Total sucess = CALCULATE(
COUNT(
Sales[Sale ID]),
Sales[Sucess] = "Yes")

Total Sales = COUNTROWS(Sales)

Success % =
DIVIDE(
[Total sucess],
[Total Sales ])

4. Success % (My Location) =
CALCULATE(
[Success %],
ALL(Sales[Sales Rep]))

I was expecting [Success % (My Location) ] (#4 above) to give me the the middle column of the chart above, but it actually gives me the right one (all locations).

So when I filter rep (Ann for example), the query shows that this rep location is Europe. I want to create a measure that when the user is filtered, the user location is also filterred without the need to actively filter the location.

Hope this is clear..

Thanks!

Re: choose the right filter context in measure to compare to wider category

PaulOlding — Mon, 15 Nov 2021 16:01:28 GMT

Hi Anonymous

It's possible to create a report for this analysis without any complex DAX. Your Success % measure would do it.

You'd need 3 visuals, one for Sales Rep, one for Location and One for Overall. The Sales Rep visual and Location visual should have the edit interactions property set to 'no interaction' with the Overall visual:

The user would be able to click on a sales rep in the Sales Rep visual and, with cross-highlighting, show how that sales rep compares with their Location

Re: choose the right filter context in measure to compare to wider category

PaulOlding — Mon, 15 Nov 2021 16:10:07 GMT

Having said that, this is the DAX forum so let's see if we can get your exact output...

The first issue is that values that appear in the x-axis of a chart need to all be in a single column. The desired chart has a mix of Sales Reps, Locations and an 'All Locations', so we'll need to create a table that has a column containing all these values. Here's some DAX to create that table:

Chart Levels = UNION( ROW("Name", "All Locations", "Level", "All", "Order", 3), ADDCOLUMNS( VALUES(Sales[Sales Rep]), "Level", "Sales Rep", "Order", 1 ), ADDCOLUMNS( VALUES(Sales[Location]), "Level", "Location", "Order", 2 ) )

Now we can use Name column of this table in the Axis field well of the chart

The measure is:

Successful Sales Pct = VAR _Name = SELECTEDVALUE('Chart Levels'[Name]) VAR _Rep = SELECTEDVALUE(Sales[Sales Rep]) VAR _Location = SELECTEDVALUE(Sales[Location]) VAR _Result = SWITCH( SELECTEDVALUE('Chart Levels'[Level]), "All", DIVIDE( CALCULATE(COUNTROWS(Sales), REMOVEFILTERS(), Sales[Success] = "Yes"), CALCULATE(COUNTROWS(Sales), REMOVEFILTERS()) ), "Location", DIVIDE( CALCULATE(COUNTROWS(Sales), REMOVEFILTERS(), Sales[Location] = _Name, Sales[Location] = COALESCE(_Location, _Name), Sales[Success] = "Yes"), CALCULATE(COUNTROWS(Sales), REMOVEFILTERS(), Sales[Location] = _Name, Sales[Location] = COALESCE(_Location, _Name)) ), "Sales Rep", DIVIDE( CALCULATE(COUNTROWS(Sales), Sales[Sales Rep] = _Name, Sales[Sales Rep] = COALESCE(_Rep, _Name), Sales[Success] = "Yes"), CALCULATE(COUNTROWS(Sales), Sales[Sales Rep] = _Name, Sales[Sales Rep] = COALESCE(_Rep, _Name)) ) ) RETURN _Result

That'll allow us to use Sales[Sales Rep] as a slicer and get the following output when a rep is selected:

Re: choose the right filter context in measure to compare to wider category

Anonymous — Tue, 16 Nov 2021 08:05:33 GMT

PaulOlding thanks a lot. showing in different visualizations is possible, but becuase the user needs it for comparation of one rep to the wider population, I wanted to show in one.

Your solution worked for the sample I provided, for some reason it doesn't work on my actual data, (it only shows the bar of all locations). I will try to better understand the measure you wrote and what needs to be adjusted.