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 all,
I have a table where each line represents a transaction (a sale), whose prices I want to rank by product, but only for some cases.
This table is what I have now, but not what I want.
(The horizontal lines are only to better visualise the ranking of each product.)
This is my formula for rank column:
Rank =
VAR CurVal = Table1[Price]
RETURN
CALCULATE(RANKX( Table1, Table1[Price], CurVal, ASC, Dense), FILTER(Table1,Table1[ProductID]=EARLIER(Table1[ProductID])))
Goal: to get the rank for each price (lower to highest), for each product… but there´s a catch: the flag. The flag is “on” when a certain characteristic is verified. I only care about ranking for a product if there is a transaction with the flag and if that transaction has the lowest price with the flag on.
Moreover, I would like to ignore prices equal zero (like ProductID 4) when ranking.
Therefore, the output that I want looks like this:
Can anyone help me?
Thanks in advance.
Solved! Go to Solution.
Hi @valcat27 ,
You can create the following calculated column:
Rank =
VAR A =
SUMMARIZE (
'Table',
'Table'[TranscationID],
'Table'[ProductID],
'Table'[Flag],
'Table'[Price],
"Rank",
IF (
'Table'[Price] = 0,
BLANK (),
RANKX (
FILTER (
ALL ( 'Table' ),
'Table'[ProductID] = EARLIER ( 'Table'[ProductID] )
&& NOT ( 'Table'[Price] = 0 )
),
'Table'[Price],
,
ASC
)
)
)
VAR b =
SUMX (
FILTER (
A,
'Table'[ProductID] = EARLIER ( 'Table'[ProductID] )
&& NOT ( ISBLANK ( 'Table'[Flag] ) )
),
[Rank]
)
RETURN
IF (
b = 1,
SUMX (
FILTER ( A, 'Table'[TranscationID] = EARLIER ( 'Table'[TranscationID] ) ),
[Rank]
),
BLANK ()
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @valcat27 ,
You can create the following calculated column:
Rank =
VAR A =
SUMMARIZE (
'Table',
'Table'[TranscationID],
'Table'[ProductID],
'Table'[Flag],
'Table'[Price],
"Rank",
IF (
'Table'[Price] = 0,
BLANK (),
RANKX (
FILTER (
ALL ( 'Table' ),
'Table'[ProductID] = EARLIER ( 'Table'[ProductID] )
&& NOT ( 'Table'[Price] = 0 )
),
'Table'[Price],
,
ASC
)
)
)
VAR b =
SUMX (
FILTER (
A,
'Table'[ProductID] = EARLIER ( 'Table'[ProductID] )
&& NOT ( ISBLANK ( 'Table'[Flag] ) )
),
[Rank]
)
RETURN
IF (
b = 1,
SUMX (
FILTER ( A, 'Table'[TranscationID] = EARLIER ( 'Table'[TranscationID] ) ),
[Rank]
),
BLANK ()
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |