topic Top/Bottom Measure giving wrong value in DAX Commands and Tips

Top/Bottom Measure giving wrong value

Anonymous — Sat, 25 Mar 2023 08:49:53 GMT

Hi Everyone,

I have a measure to calculate Top/Bottom/All as follows :

Top/Bottom =

VAR NValue =

SELECTEDVALUE ( 'Drop Down '[Values] )

VAR Ranking_TOP_CUST =

RANKX (

ALLSELECTED ( 'Sales'[Product Group] ),

[Current Period Revenue],

DESC

)

VAR Ranking_Bottom_CUST =

RANKX (

ALLSELECTED ( 'Sales'[Product Group] ),

[Current Period Revenue],

ASC

)

VAR Result =

SWITCH (

SELECTEDVALUE ( 'Top/Bottom'[Selection] ),

"Top", INT ( Ranking_TOP_CUST <= NValue ),

"Bottom", INT ( Ranking_Bottom_CUST <= NValue ),

"All", 1

)

RETURN

Result

Top/Bottom[Selection] column has values : Top,Bottom,All

Drop Down[Values] column has values : 2 to 30

I have then pulled this measure into my table as a visual level filter and set it as : Show Item when Value is 1

Now, I have a Year slicer and a Previous Period Revenue measure. When I apply the Top/Bottom Measure ,and select a Year the Previous Period Revenue is showing minor difference than it should .

Previous Period Revenue =

VAR YTD_ = CALCULATE([Sales Out DMS],INIT_RAW_SG_VN_CDS[FIS_YR]=max(INIT_RAW_SG_VN_CDS[FIS_YR])-1,ALL(INIT_RAW_SG_VN_CDS[FIS_YR],INIT_RAW_SG_VN_CDS[FIS_QTR],INIT_RAW_SG_VN_CDS[FIS_MTH],INIT_RAW_SG_VN_CDS[FIS_WK]))+0

I am not able to identify what is wrong.. Would appreciate help..

Thanks in advance...

Swathi

tamerj1 amitchandak

Re: Top/Bottom Measure giving wrong value

Greg_Deckler — Sat, 25 Mar 2023 11:56:45 GMT

Anonymous Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, 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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Top/Bottom Measure giving wrong value

Anonymous — Sat, 25 Mar 2023 12:14:26 GMT

Hi Greg_Deckler

My requirement is as follows:

i have a table visual which has Account Name, current period revenue and previous period revenue as values.
I need to give 2 slicers .

1) to select Top/Bottom/All

2) to select number between 2 and 30

Now based on the slicer selections, my table visual should show change.

eg: if I choose All then it should show all Account Name.

if Top and 5 is selected, then table should show top 5 accounts with highest current period revenue. And similar for Bottom as well.