Hi, I have a quick question and am wondering if this is possible to do in PowerBI. I want to have product rank by sales and use this Rank measure as a slicer on the page. When I select Top N from slicer, it will only show the metrics for TopN.
Could you please help?
Thanks
Mike
TopN_Products.pbix (57.1 KB)
Mike,
Is this the result you’re looking for?:
If so, I created a what-if parameter called Top N products, and used it in the filter condition of the following measure.
Rank Prod by Sales =
VAR ProdRank =
CALCULATE(
RANKX(
ALL( 'Table'[ProductID] ),
[Total Sales],,
DESC,
Dense
)
)
VAR Result =
IF( HASONEVALUE( 'Table'[ProductID] ),
IF(
ProdRank <= 'Top N Products'[Top N Products Value],
ProdRank,
BLANK()
),
BLANK()
)
RETURN
Result
I hope this gets you what you need. Full solution file attached.
Hi, @BrianJ Thanks for quick response. This is very close to what I wanted. However, my challenge is the cards on the top. When I change the slicer, the measures in the cards did not update accordingly. Do you have any solution? I searched on Internet, but could not find any way to dot it.
@Mike ,
Okay, I’ve got the cards computing correctly dynamically based on the top N slicer selection:
The key here is building a virtual table (vTable in the measures I used) that mirrors the table in the screenshot above. Once that table is computing correctly dynamically based on the top N slicer selection, it’s a relatively simple matter to do a COUNTROWS or SUMX on that virtual table to obtain the card measures. The challenging aspect is getting the virtual table DAX correct, and for that it is extremely helpful to use a tool that can easily visualize the virtual table using a DAX query – either DAX Studio or Tabular Editor 3 work beautifully for this.
Here’s the percent out of stock measure as an example, but all the others use the same structure, but with a different Result variable.
Out of Stock % Ranked =
VAR vTable =
FILTER(
ADDCOLUMNS(
ALL( 'Table'[ProductID] ),
"@RankProd", [Rank Prod by Sales],
"@InvStatus", [Inventory Category],
"@TotSales", [Total Sales]
),
[@RankProd] >= 1
)
VAR Numerator =
COUNTROWS( FILTER( vTable, [@InvStatus] = "Out of Stock" ) )
VAR Result =
DIVIDE( Numerator, [Top N Products Value], BLANK() )
RETURN
Result
I hope this is helpful. Full revised solution file posted below.
– Brian
eDNA Forum - TopN_Products Solution.pbix (60.7 KB)
Hi, @BrianJ Thank you very much! Have a topN as a filter to filter out the results at the page level is always a challenge. I really appreciate your time and effort to help me through. It looks like virtual table is a key for this solution. I will replicate your solution to my true data set and learn how to build it. Once again, thank you very much!
Mike
Hi, @BrianJ Your solution is awesome. You recommended the tools last time and I have downloaded all of them. Will need time to learn how to use them. One quick question, but not sure how hard it could be. As you can see the tables I have on the screenshot. When I have produce category in the table, it seems not work. Do I need to bring the product category to the virtual table to make it work? on the table, it is nice to be able to show for the Top N products, how many products in each category and how many items are out of stock in each category as well.
I am so sorry for so many questions.
Thanks a lot!
@Mike ,
Okay, I think I have the whole thing now working dynamically off of the Top N Products slicer:
No surprise that all that the red highlighted table gave you trouble – it did me as well. Though it looks on the face of it like a straightforward calculation, it’s actually one of the more difficult measures I’ve ever worked on. After spending far more time on it that I would care to admit, I got close on the Products #Ranked 3 measure, but couldn’t get it quite over the goal line. I had to break the glass on the bat phone to the Netherlands to get @Melissa’s help on it as well (thanks, Melissa!)
Here’s what the beast looks like in final form:
Products # Ranked 3 =
VAR SelCat =
SELECTEDVALUE( 'Table'[Category] )
VAR vTable =
FILTER(
ADDCOLUMNS(
ALL( 'Table'[ProductID] ),
"@RankProd",
CALCULATE(
[Rank Prod by Sales],
REMOVEFILTERS( 'Table'[Category] )
),
"@InvStatus", [Inventory Category],
"@TotSales", [Total Sales]
),
[@RankProd] >= 1
)
VAR nTable =
NATURALINNERJOIN(
vTable,
'Table'
)
RETURN
IF(
HASONEVALUE( 'Table'[Category] ),
COUNTROWS(
FILTER(
nTable,
'Table'[Category] = SelCat
)
),
[Products # Ranked]
)
The Out Of Stock by Category measure follows a very similar pattern, and the % Out Of Stock is just a basic DIVIDE function of the prior two measures.
I hope this hits the mark. Full revised solution file attached below.
Really interesting problem. Enjoyed working with you (and Melissa) on this one.
– Brian
eDNA Forum - TopN_Products Solution 3.pbix (62.6 KB)
Hi, @BrianJ I am so sorry for all the trouble and appreciate all the time and help from you and @Melissa . I cannot thank you both enough for providing me the guidance and solution on this problem.
Like you said, when I got this request from business, I thought that was a very easy one, even though I knew the TopN would be a challenge topic in PowerBI. I knew Sam has some training video in his membership learning portal, so I told myself that I could check Sam’s videos and could find some tips. But when I started to work on it, I realized it was much harder than I had thought. It is a very common question to a lot of companies, but it seems that there is no such solid solution on the web. It will be lovely if you can take this problem and solution and have a video to cover this topic so that other people can also benefit from this learning. When I checked your solution, it is very advanced technical, from some DAX functions to virtual table, and to the DAX Studio or Tabular Editor tool you suggested.
@BrianJ Once again, thank you very very much! I will take you solution back and I will have to break it into small pieces to understand the logic you and @Melissa applied. You guys are truly amazing!
Mike
@Mike ,
No need at all to apologize. I truly love working problems on the forum, and really enjoy the ones that present a major challenge. I’m incredibly stubborn, so will pound away at these until I’ve exhausted every tool my toolbox. Then I’ll move to this strategy for a while -
It never works, but makes me feel like I’ve tried everything before picking up the bat phone…
I’ll give some thought to what in this solution is of general enough interest to warrant a video.
In terms of learning to use external tools to visualize virtual tables, I think that’s a critically important skill to progress in your DAX skills. The two biggest returns on investment IMO for intermediate Power BI users are: 1) strengthening your Power Query skills and learning some basic M coding - I know I’m on this soapbox a lot, but the better you get with PQ, the easier your DAX gets - to the point where complex DAX is a rarity in most of my work reports and Challenge entries; and 2) strengthening your ability to work with virtual tables - this is game-changing in terms of what you can get DAX to do when you develop comfort with the key virtual table functions. It used to be that doing virtual table work was sort of like operating in the dark - you could use things like COUNTROWS and FIRSTNONBLANK/ LASTNONE blank to get a general idea of whether your virtual table was of the right length/shape, but you couldn’t see your table without having to materialize it in a physical table and switch between that table and the (garbage) native DAX editor. Now, you can see the tables right on screen and have them update automatically as part of the development environrnments in DAX Studio and TE3.
To see how to use DAX Studio to best effect in this way, I cannot recommend @AntrikshSharma 's videos highly enough. He’s got one coming out this week, and watching him do virtual table work in DAX Studio is like watching Duke Ellington play piano - total mastery of the instrument.
Enterprise DNA Expert @Nick_M also has some outstanding content in the Enterprise DNA Optimizing DAX course on using DAX Studio, and @AntrikshSharma is working on a TE3 course for us that I’m incredibly excited for. I’m also using TE3 in most of my recent and upcoming videos to demo the capabilities and get viewers used to writing and debugging DAX in it. So, lots of good ways within the Enterprise DNA “ecosystem” to improve your skills in this area.
Thanks, @BrianJ . You are absolutely right. I am excited to learn more DAX Studio and TE3 and thrilled to see the virtual table materialized on screen. Thanks for sharing the recommended video and looking forwards to the upcoming DAX courses on DAX studio.
Cheers,
Mike
Hi, @BrianJ , Your solution works perfect. Thanks a lot!
I have one question not related to it, but I noticed on the Dashboard and wondering if you know the reason. For example, if I remove the alternateResult 5 from the formula
Top N Products Value = SELECTEDVALUE(‘Top N Products’[Top N Products],5)
Then it will change the calculation of Product# Ranked 3.
Do you, by any chance, know why that happens? I am just curious. If you are busy, then please ignore my question.
Thanks!
Mike
That second parameter of SELECTEDVALUE Is extremely important for error trapping and debugging. What that does is provides a value to return (in this case, 5) if either no selection is made or the selections made do not resolve down to a single value.
However, if the value of the slider is being picked up properly, that alternative value should not come into play since the slider by design only returns a single scalar value. Are you sure that the slider value is being picked up properly in the report? (I think in the version I sent you that slider parameter maxed out at 20)
Note: incredibly coincidentally, I’ve got a video on this exact issue regarding the second parameter of SELECTEDVALUE coming out next week.
I hope this sheds some light on the issue. If you can send me your revised PBIX file I can probably better diagnose what’s going on here.
– Brian
Hi, @BrianJ ,thanks. You are right that I changed your Max value.
When I tried to replicated your measures into my report, accidently I forget to add the second parameter to the SELECTEDVALUE function and the value of the measure was completely off. I spent almost one night trying to figure out what the issue could be and eventually I found that was caused by the missing second parameter. That was not fund, but it was a good learning experience.
Glad you will have a video on this one and I am looking forwards to watching it when it is out.
Thanks alot!
Mike
@Mike ,
The thing that concerns me is that if the measure is picking up the slider value properly, it shouldn’t matter whether you include the alternate parameter or not. The function should not be getting to that point since the slider should always be providing a conforming value that fulfills the first condition.
Can you do me a favor? Stick a card on the relevant page of your report and drop the measure that harvests the value of the slicer into that card. Move the slicer back and forth and see if the card changes appropriately or just displays the alternate value.
– Brian
@BrianJ , Hi Brain, it looks the card is working well to capture the data from the slicer.
I used your file and removed the second parameter from the SELECTEDVALUE function, it show the same probelm.
What I did found during my crazy debugging process was that I could see the output (5 rows, I was wondering why when I was debugging) in Dax Studio when I run the virtual table from the measure Product # Ranked 3.
FILTER(
ADDCOLUMNS(
ALL( 'Table'[ProductID] ),
"@RankProd",
CALCULATE(
[Rank Prod by Sales],
REMOVEFILTERS( 'Table'[Category] )
),
"@InvStatus", [Inventory Category],
"@TotSales", [Total Sales]
),
[@RankProd] >= 1
)
however, after I removed the 5 from the SELECTEDVALUE function, the Virtual table has no output at all.
Not sure how that was related and that was something I was struggling with yesterday. Hope that can help a bit.
Thanks a lot!
Mike
eDNA Forum - TopN_Products Solution 3.pbix (62.6 KB)
