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.
Hi guys
Need help before I pull all my hair out
The ranking works fine for everything except for the red circled, it should be 1 instead of 2
Sales Rank =
IF (
ISINSCOPE ( 'Product'[Product Category] ),
RANKX ( ALL ( 'Product'[Product Category] ), [Sales Amount],, DESC ),
IF (
ISINSCOPE ( 'Product'[Product Line] ),
RANKX ( ALL ( 'Product'[Product Line] ), [Sales Amount],, DESC ),
BLANK ()
)
)
Here is the link of the pbix file on google drive
https://drive.google.com/open?id=1lBfXbT8ZFWSEcbKizBypfrGrJez4Ptp7
Thanks
Solved! Go to Solution.
That is very odd behavior, I can only think that it has something to do with [Product Category] "Fuses" have a sales amount larger than the #2 ranked [Product Line].
However, switching it to to using HASONEVALUE seems to fix it.
Ranking = IF ( HASONEVALUE ( 'Product'[Product Category] ), RANKX ( ALL ( 'Product'[Product Category] ), [Sales Amount] ), IF ( HASONEVALUE ( 'Product'[Product Line] ), RANKX ( All ( 'Product'[Product Line] ), [Sales Amount] ) ) )
Hi @Anonymous
That looks strange, yes. I don't see anything wrong with your code. In fact, to make it stranger, I was running some tests and realized by chance that by adding another measure to the matrix visual, your measure all of a sudden yields the correct result. If I take my mock measure [Sales rank V3] off the visual, your measure yields the wrong results again. Try it on "Page 2" that I've added to your original file (see attachment). Add and remove [Sales Rank V3] to the visual and see how the result for "Circuit protection" changes between 1 and 2
Maybe knowledgeable people like @Zubair_Muhammad, @MFelix, @Greg_Deckler can have a look and help here
That is very odd behavior, I can only think that it has something to do with [Product Category] "Fuses" have a sales amount larger than the #2 ranked [Product Line].
However, switching it to to using HASONEVALUE seems to fix it.
Ranking = IF ( HASONEVALUE ( 'Product'[Product Category] ), RANKX ( ALL ( 'Product'[Product Category] ), [Sales Amount] ), IF ( HASONEVALUE ( 'Product'[Product Line] ), RANKX ( All ( 'Product'[Product Line] ), [Sales Amount] ) ) )
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 |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |