Why 1 measure is right and the other wrong?

[Anonymous]

Hi All,

So, i am trying to create a custom filter that enables us to filter our products by their rank in regards to the revenue.

The measures used are (Refer image below for more clarity):- 

1) M_Revenue = SUMX(sales, sales[quantity] * sales[price])

2) M_Rank = RANKX(ALL(products), [M_Revenue])

3) M_TopN_Revenue_UsingFilter = CALCULATE([M_Revenue], FILTER(products, MAX(TopNTable[Max]) >= [M_Rank] &&

MIN(TopNTable[Min]) <= [M_Rank])

4) M_TopN_Revenue_UsingIf = IF([M_Rank] <= Max(TopNTable[Max]) && [M_Rank]>= MIN(TopNTable[Min]), [M_Revenue], BLANK())

Now, the question is that using 3rd measure all my visuals are performing fine but the 4th measure instead of 3rd one doesn't work.

Would anyone know why? Why is it that when i am using 4th instead of 3rd measure that my Totals don't show up for the TopN categories- in both the visuals even though it shows the correct Revenue values for the appropriate TopN category selection in filter. Refer the image below: 

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

In my opinioin,

FILTER is an iteration function and it calculates row by row. So, inside the measure, the calculation is considering PRODUCT by PRODUCT from the perspective of ranking -> when the ranking criteria for one product meets, then shows the result row by row -> when the ranking criteria for one product does not meet then no-show.

IF function sees the result of each row. So, when seeing product by product, it seems it is the same as filter function. However, when coming to the last line, which is the TOTAL, if function sees the total amount then checks whether it meets the criteria. So, if you select the top 10, it works because  139,708 (which is top 1) is within the top10 criteria. However, when you select 11~20, it shows blank because you wrote inside the measure that if the rank (which is TOP1 in this case) is not in between 11 and 20 then blank.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

@mahoneypat You are right, when i select top 10, i do see values- but why is it that with measure number 4- other TopN categories do not work. Even with Top 10- the subtotal is giving the full total for all categories combined not for top 10.

[mahoneypat]

I reproduced your scenario on a similar model (please provide a link to a pbix if not), and this measure seems to do the trick.

Dynamic TopN =
VAR minRank =
    MIN ( TopNTable[Min] )
VAR maxRank =
    MAX ( TopNTable[Max] )
VAR TOPNcount = maxRank - minRank + 1
VAR ProductValues =
    VALUES ( 'Product'[Product] )
VAR SalesTable =
    ADDCOLUMNS ( ALL ( 'Product'[Product] ), "cSales", [Total Sales] )
VAR TOPNX2 =
    TOPN ( TOPNcount, TOPN ( maxRank, SalesTable, [cSales], DESC ), [cSales], ASC )
RETURN
    SUMX ( FILTER ( TOPNX2, 'Product'[Product] IN ProductValues ), [cSales] )

Pat

[mahoneypat]

Do you see a Total value when "Top10" is selected?  Your total is likely returning as 1, so it is excluded by your filter in #4.  A couple other suggestions:

1. You should leverage variables in these expressions (even create a variable within the FILTER of #3) to potentially improve performance (i.e., calculate your M_Rank and your Min/Max values once).

2. Probably won't be any better (after you put in variables) but you could also consider an expression with KEEPFILTERS and nested TOPNs (using min and max slicer values, one with ascending, one with descending).

Regards,

Pat

[Anonymous]

@mahoneypat Hello Sir, At first i did not understand when you responded "Your total is likely returning as 1, so it is excluded by your filter in #4". But now with more explanation from @Jihwan_Kim - I do appreciate your point. Thank you both for helping me improve my understanding of Power BI.