Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Community -
I am in need of some help with my TopN slicer and how it interacts with a Matrix. I have a measure that is summing my TopN sales amounts by Item which is working perfect. But when I put my items into a matrix at the second level of rows (Brand --> Item), my Brand is still summing ALL of the items associated with that brand rather than just the TopN selection, even though my Items are only showing the TopN selected in my slicer. Below is my current DAX measure that I am using.
I would have though the matrix would just sum up anything at the lower level and it would dynamically adjust to whatever TopN selection chosen, but it does not.
Any help would be greatly appreciated!
Thanks
Ryan
TopN $ Flavors =
VAR SelectedTop = SELECTEDVALUE('TopN'[TopN])
RETURN
SWITCH(TRUE(),
SelectedTop = 0, [Total Sales ($)],
RANKX (
ALLSELECTED( 'Competitor Sales'[ITEM_DESCRIPTION] ),
[Total Sales ($)]
)
<= SelectedTop,
[Total Sales ($)]
)
@v-alq-msftthat worked, I just changed the visual control to use my measure ranther than the RANK you included.
Now, one last question branching off of this.....I have a trend chart by BRAND, but being I do not have Items as a dimension in the visual, it is back to summing up ALL sales rather than just TopN Item Sales. Is there any work around for that by chance?
Thanks
Ryan
Hi, @ryan_b_fiting
You may modify the 'Rank' measure as below.
Rank =
RANKX(
CALCULATETABLE(DISTINCT('Table'[Brand]),ALLSELECTED()),
CALCULATE(SUM('Table'[Sales]))
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-alq-msft thanks, but I get the same result as before. It does not sum up the TopN items by brand, but rather sums ALL items by brand for the specific date. See below screen shot
The way your TopN measure works is different from what I need (you are showing TopN overall, where I need TopN Items by Brand). Other than that I followed your steps exactyl
Hi,
I cannot spot any mistake in the table there. I see the Top 4 items per brand and the total (112,164.73) seems to be the summation of only those 4 items. What is the problem?
@Ashish_Mathur It branched off into another question from the original Matrix issue which I then posted the issue here:
https://community.powerbi.com/t5/Desktop/TopN-Only-SUM-TopN-Items-for-Category-Total/m-p/1018556
@Ashish_Mathuryou are correct, the matrix is working perfectly. The issue comes with the trending line graph. That is by brand (items are not a dimension there). But I want the values in the line graph to reflect only the sum of the TopN items associated with the brands. So I would expect all data points for the top brand in the line chart to total the 112,164.73, but they do not.
Hi, @ryan_b_fiting
Based on your description, I created data to reproduce your scenario.
Table:
You may create a what-if parameter from 1 to 5 and measures as below.
Rank =
RANKX(
ALLSELECTED('Table'),
CALCULATE(SUM('Table'[Sales]))
)
TopNValues =
var _topn = 'TopN'[Parameter Value]
return
SWITCH(
TRUE(),
_topn = 0,SUM('Table'[Sales]),
[Rank]<=_topn,SUM('Table'[Sales])
)
visual control = IF([Rank]<=SELECTEDVALUE('TopN'[TopN]),1,-1)
Finally you may put the measure 'visual control' in the visual level filter and then get the result.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-alq-msftthanks for this, I will test it out. But before I do I want to ask, I am looking to have TopN Items for each brand, is this doing that? Or is this just taking the TopN and disbursing it based on the ranking?
So if I select Top 5, I want to see the top 5 items in each brand and have the sum of the brand in the matrix equal the 5 items that re in the top for each brand.
Refer if this can help
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |