Dynamic Segmentation With Dynamic Parameters Video & PBIX File

Power BI
1

Hello - I am trying to utilize the content from this video, but I noticed that it looks like there might be some sort of issue with the logic. If you look at the file (or the video), you’ll notice:

[Top Customers] + [Bottom Customers ] <> Total Revenue in some cases (e,g, abilene)

image
image.png708×73 2.7 KB

Can someone take a look at this and let me know what the correct logic would be? Additionally, I am seeing that the subtotal of top and bottom customers is correct, but in a table, it seems to display values in the top customer column , that should really be in the bottom. Please advise, and thanks for your help! This is going to be great once successfully implemented.

image
image.png895×207 23.9 KB

2

To add some more info on the second snip it, the bottom customers total seems to = the overall total, when I was thinking it should be the overall less the top.

Thanks,

Nick

3

Hi @nickjordan32

I don’t have a solution for you, because I’m heading out but I do know the reason why this is happening and that’s because we’re not accounting for ties. As you can see there are 2 rows that are counted twice.

image
image.png943×309 17.6 KB

Important note.
This will only happen to Abilene when you Rank by 5 - in all other cases the numbers will all add up.

4

Hi Melissa - Thank you for clearing that one up. When you get the chance, can you look at my formula and let me know if you see anything that would be causing the bottom customers to calculate the total earned revenue as opposed to the earned revenue for the difference between total earned revenue and top customers?

I should be working as outlined below but I can not seem to figure out why. I feel it has something to do with the top n piece of the formula, but can not say for sure. Let me know, thanks!

image
image.png807×87 4.36 KB

e.g.

  • 10 customers total

  • customer rank value = 1

  • top customers (revenue) = earned revenue for the #1 customer

  • bottom customers (revenue) = earned revenue for customers 2-10

5

Okay so dealing with ties in TOPN can be achieved by adding RAND() to the OrderBy_Expression
This returns a random number greater than or equal to 0 and less than 1. Altough the number that is returned changes each time the function is recalculated - that will not impact the end result.

I would suggest implementing this change to both Top Customers and Bottom Customers measures, like so:

Top Customers = 
CALCULATE( [Total Revenue],
    TOPN( [Customer Rank Value], VALUES( Customers[Customer Names] ), [Total Revenue] + RAND(), DESC ))

.
With this result:
image

As for your 2nd issue.
Can [Earned Revenue] be negative? The logic looks sound so maybe there is another issue… can you post a sample?

1 Like
6

Hi Melissa - I would send over but it would take very long to sanitize. Would it be possible that the formula does not consider if a certain customer has revenue in multiple regions? I have noticed in many cases when the bottom customers revenue = total revenue, there has been a customer that also has revenue in a different region… Let me know, thanks!

Nick

7

Hi Nick - That’s hard to tell without the PBIX because there are always several elements in play.
But I’m sure if you analize the numbers you can determine if that is indeed the case, much like I’d done to identify the ties. Let me know what you find.

8

Hi @nickjordan32, we’ve noticed that no response has been received from you since the 14th of February. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!

9

Hello - I ended up just not utilizing the functionality in question for my report. I never found a solution, but I don’t think I provided you guys what was needed to help troubleshoot… If I get around to researching and fixing I will reply to this thread.

Thanks!
Nick

10

Oof, is introducing a random number into the orderby really the best option? There is still a non-zero (~10^-16) chance of getting a tie each time the measure is recalculated. This feels like a duct tape fix. I would be interested to hear other potential solutions to the tie problem.

11

@CStaich,

Alternatively, it seems you could use an index field as your OrderBy_Expression to break ties. Because index numbers are unique by definition, no chance of a re-tie.

  • Brian
12

Hi @nickjordan32, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!"