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
Mtelf24
Helper I
Helper I

Representing Retention Bands (Using measure in axis)

I have two tables, one is an opportunity table, and one is a salesperson table. They are joined in PowerBI on the ID with the salesperson table being on the one side of one to many.

The salesperson table has one person per row

The opportunity table can have the salesperson multiple times (multiple opportunities can belong to a salesperson)

The opportunities have dates around proposals, due dates and when they are closed as won/lost.

 

I have a Won vs Lost measure (retention) on the opportunity table that works fine in a matrix. I can have the name of the salesperson from the salesperson table and change the date values from the opportunity table and the retention percentage will update accordingly.

 

What I'm not quite sure how to represent from this is a clustered column chart with bandings (e.g. 20-30 = 20-30% retention, 30-40 = 30-40% retention and so on). I then want to have the number (count) of the salesperson who sit in each banding at which point a user can click on a banding and the matrix will update with the salespersons who may be performing in a certain band, e.g.BandExample.PNG

If I take the retention calculation and turn it into a column on the salesperson table I can easily create a 'band' calculated column (because there is only one row per salesperson), but it only works in the context of the entire dataset. If I want to filter based off dates on the opportunity table the retention calculation measure will update if its the opportunity table one, but its copy is static on the salesperson table.

 

I don't know if I need to do something abit more fancy with RELATED or FILTER when I 'copy' the measure from opportunities to salespersons or if I'm approaching this from completely the wrong direction/angle (i.e. I shouldn't be bothering with the salesperson table at all given its just a unique list (summary) of the sales peoples names and little else) to produce the graph above and should be trying to do something along the lines of a distinct count of salespersons on opportunities (to prevent multi-counting) and then somehow work out a way to use a bands measure (which I have created on the opportunity table) in the Axis.

1 ACCEPTED SOLUTION

Hi @Mtelf24 ,

 

test.gif

 

Please refer to the pbix file.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

View solution in original post

7 REPLIES 7
Mtelf24
Helper I
Helper I

Hi @v-deddai1-msft

 

You are amazing! Thank you - this almost solves it - your example works perfectly, but there must be something hidden in the background you did that I missed, because when I try to recreate what you've done, it still doesn't quite work, and I can't work out what the last missing piece is.

 

To conclude to solve it you:

  1. Added min and max columns to the Retention Band table to help with your measure calculations
  2. Created a Banding Measure on the Retention Band table that
    • Stored a variable using the SUMMARIZE function, with the group by being the salesperson name on the salesperson table, called it "retention" and evaluated it with the 'Retention Measure' on the opportunities table.
    • Used COUNTX alongside FILTER to pass the context of the variable as the table and then compare the summarized value ("retention") against the min and max values (utilising greater or less than operators alongside MAX function) of the retention band table in order to determine where to count the salesperson.
  3. Replaced the Axis with the retention band labels on the retention band table and replaced the distinct count measure on the opportunities table with the new Banding Measure you created

But when I try to do this in the copy I tried (see below for link), it still doesn't 'cross' filter when selecting the bars. As far as I can tell the measure called 'Measure' you added is not actually used. I've tried scanning as much as I can comparing between your pbix and the one where I've copied your formula and extra columns and I can't work out what is missing? I'm stumped!

I've checked data types, settings on the visuals, the relationship mapping settings, but I'm afraid I still can't work out what you did that was clever enough to make it filter the matrix when selecting the bar.

My attempt to copy what you've done is 

https://drive.google.com/file/d/1pEtCa7F_4huo1g6LcWtJfsHmAp0um4Zd

I have compared clicking on the bar chart in my own copy of what you've created and then clicking on your working example with the performance analyzer, and can see when comparing the DAX Queries, that there is a key difference. Your one runs the following in addition:

 

VAR __ValueFilterDM1 =
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'SalesPerson'[Salesperson Name],
__DS0FilterTable,
"Won_Count_Measure", 'Opportunities'[Won Count Measure],
"Lost_Count_Measure", 'Opportunities'[Lost Count Measure],
"Retention_Measure", 'Opportunities'[Retention Measure],
"Banding_Measure", 'Opportunities'[Banding Measure],
"Measure", IGNORE('Retention Bands'[Measure])
)
),
[Measure] = 1
)

 

So I can see that you did actually create the measure called 'Measure' for good reason, but I can't work out how you got it to apply itself to the column chart as when I select the bar chart in your example the 'Measure' is not highlighted as being in use.

v-deddai1-msft
Community Support
Community Support

Hi @Mtelf24 ,

 

If you want to your retention percentage will update accordingly and band them in the clustered bar visual. The calculted column can't be the approach. You need to use summarize to calculated your retention percentage dynamically based on the date fillter from opportunity table and create a new band table to fix the band on the x-axis.

 

And if you want update with the salespersons who may be performing in a certain band in the matrix. The direct visual interaction  will not work in your situation. You need to create an visual level filter in your matrix.

 

All the options above is based on my understanding on your description. If you need accurate help, provide your sample pbix file is the only way.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

Hi Dedmon,

 

I have created an example as there was lots of other non related data in the real one - but the copy demonstrates the same 'issue' I've uploaded it to: https://drive.google.com/file/d/1zaL1t_0pI5Y7dq4wsFHASzgE9ibXqq7l/view?usp=sharing

 

You should see a bar chart which uses a measure to count the salesperson and then a grouping column from the salesperson table. 

It works fine only in the scenario where a user does not change the 'FiscalYear' filter. You can see the number of salespeople who fall into each retention band and if you click the bar you get the list of salespeople who fall into that band in the matrix below.

However because of the data (and structure) this only works because its using a banding on the salesperson table which is a summary of all the data. As expected the bar chart doesn't update when the FiscalYear filter is selected as there is no Fiscal Year date on the salesperson table - if I did try something like that then the salesperson rows would no longer be unique on that table (as we would have to break down their retention by multiple years). Alternatively one could create extra columns for each year on the salesperson table (an example for 2021/22 is included in the pbix) but I can't see how that can work in a user friendly way as that would mean creating a bar chart for every single year/date etc I would want to represent.

Of course as the matrix uses measures from the opportunities table it updates appropriately when the year is selected, but I hope to be able to apply that to the bar chart if possible.

Hope my example file helps with understanding. I added a 'Retention Bands' table as well as I did see it mentioned in a couple of other example but I'm not quite sure how to apply it to solving this issue as usually the 'bands' table did something simple like a sum of a field.

Hi @Mtelf24 ,

 

test.gif

 

Please refer to the pbix file.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

Sorry - I finally worked it out! The measure called 'Measure' is a visual level filter on the matrix and is set to = 1 and that is how we get the matrix to update when the bar chart selections are made!

 

Thank you so much for your help @v-deddai1-msft!

gdarakji
Resolver III
Resolver III

What you are looking for is called "Binning". Unfortunately, the out of the box grouping/binning functionality of Power BI does not work on measures. However, there is a workaround for this. Please check a tutorial on the following link: https://www.burningsuit.co.uk/blog/2018/06/dax-how-to-group-measures-into-numeric-ranges/

After implementing it, you can display the result in a clustered column chart instead of a table as shown in the above link.

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.