Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hanapbi
Helper I
Helper I

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

Hi,

I have the following data:

Sale IDSales RepLocationSuccess
1DanielUSYes
2MichaelCanadaYes
3YolandaUSYes
4YolandaUSYes
5LilyEuropeYes
6LilyEuropeYes
7MichaelCanadaYes
8MichaelCanadaYes
9MichaelCanadaYes
10LilyEuropeYes
11DanielUSYes
12MichaelCanadaNo
13YolandaUSNo
14YolandaUSNo
15DanielUSNo
16LilyEuropeNo
17LilyEuropeNo
18DanielUSNo
19AnnEuropeNo
20AnnEuropeNo
21AnnEuropeYes

 

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:

 

hanapbi_0-1636984461483.png

I added the following calculated measures:

1. 

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

 

2. 

Total Sales = COUNTROWS(Sales)

 

3.

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!

 

1 ACCEPTED SOLUTION

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

PaulOlding_2-1636992404376.png

 

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:

PaulOlding_3-1636992581392.png

 

 

 

View solution in original post

3 REPLIES 3
hanapbi
Helper I
Helper I

@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.

PaulOlding
Solution Sage
Solution Sage

Hi @hanapbi 

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:

PaulOlding_0-1636991958951.png

 

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

PaulOlding_1-1636992050107.png

 

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

PaulOlding_2-1636992404376.png

 

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:

PaulOlding_3-1636992581392.png

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors