Forum Discussion
Helper IIIDAX or Measure to show top largest values
when you say its wrong , is it because the total is wrong? are you filtering on anything else? or is because its not the top, you should change your asc to desc if you want the top
in terms of the measure you should be able to, for readbility though i would probably change the measure to this
Top 2 =
VAR totalSales =
SUM ( financials[ Sales] )
RETURN
CALCULATE (
totalSales,
FILTER (
VALUES ( financials[Product] ),
IF (
RANKX ( ALL ( financials[Product] ), [Total Sales],, desc) <= 2,
[Total Sales],
BLANK ()
)
)
)
Community Champions15 first you need to rank your data by you sales
then you should return only where top 10
this video is helpful ti figure it out
Helper IIIThank you vanessafvg
I followed the measure in the video but the ranking is not correct.
Firstly I created a measure to reflect to the Sales column.
Total Sales = sum(financials[ Sales])
Secondly I created the measure below
Top 2 = CALCULATE([Total Sales], FILTER(VALUES(financials[Product]), IF(RANKX(ALL(financials[Product]),[Total Sales],,ASC)<=2,[Total Sales],BLANK())))
Another question is: Do I have to create the first measure? Can I replace [Total Sales] by SUM((financials[ Sales]) in the second measure directly?
Thank you
- vanessafvg
when you say its wrong , is it because the total is wrong? are you filtering on anything else? or is because its not the top, you should change your asc to desc if you want the top
in terms of the measure you should be able to, for readbility though i would probably change the measure to this
Top 2 =
VAR totalSales =
SUM ( financials[ Sales] )
RETURN
CALCULATE (
totalSales,
FILTER (
VALUES ( financials[Product] ),
IF (
RANKX ( ALL ( financials[Product] ), [Total Sales],, desc) <= 2,
[Total Sales],
BLANK ()
)
)
)I am sorry, but this solution is wrong. When I trie this, it shows the complete total, not the topn
this does neot work:
Top 2 =
VAR TotalSalesVar =
SUM ( tblfinancials[Sales] )
RETURN
CALCULATE (
TotalSalesVar;
FILTER (
VALUES ( tblfinancials[Product] );
IF (
RANKX ( ALL ( tblfinancials[Product] ); TotalSalesVar;; desc) <= 2;
TotalSalesVar;
BLANK ()
)
)
)- s15
vanessafvg Thank you for your suggestion. Changing to desc works well.
For the second question, can I just have only one measure in which I can have to sub-measures?
From your measure, it seems not to work in my environment even when you set totalSales as a variable. totalSales seems not to passed to expression inside RANKX
- vanessafvg
s15 remove the [ brackets i think you using a variable
- novicepbixer
Advocate I
Thanks for this expression! Helped out a lot!
