Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello family ;
I want help to display in a measure the second great value and the third also
I have 2 measure of calculations I transformed the figure and absolute value
as shown in the photos below
thank you in advance
Solved! Go to Solution.
Hi @Anonymous ,
I create a simple example that you can refer to:
1. Table:
2. Measures.
Percent =
CALCULATE ( SUM ( 'Table'[Value1] ) - SUM ( 'Table'[Value2] ) )
/ SUM ( 'Table'[Value2] ) * 100
ABS Percent = ABS ( [Percent] )
3. Then I create a Rank measure.
Rank = RANKX ( ALLSELECTED ( 'Table' ), [ABS Percent],, DESC, DENSE )
4. Create measure to show 1st, 2nd, 3nd highest value.
1st highest value = CALCULATE([ABS Percent],FILTER('Table',[Rank]=1))
2nd highest value = CALCULATE([ABS Percent],FILTER('Table',[Rank]=2))
3rd highest value = CALCULATE([ABS Percent],FILTER('Table',[Rank]=3))
For more details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
If you want the Rank value changed by the Date range you selected, the measure above can meet your requirement. Because I used "ALLSELECTED" in the expression.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Not Sure I got completely. But seems like Rank can help
For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
RANKX allows you to sort your table based on a measure that you would like
So I suggest to create a virtual table with a SUMMARIZE, representing in memory your table as in the visual, then adding a RANKX. It will add a column with numbers 1 to N where 1 is the first, 2 is the second etc.
Then with a FILTER you can choose the 1st, 2nd etc.
Exactly yes; @Anonymous
I have already made the rank but what I really want is to display it in a measure like the max function which takes the 1st I want to know is what we can do a compound function to have a measure displays the 2end and 3thd value
Hi @Anonymous ,
I create a simple example that you can refer to:
1. Table:
2. Measures.
Percent =
CALCULATE ( SUM ( 'Table'[Value1] ) - SUM ( 'Table'[Value2] ) )
/ SUM ( 'Table'[Value2] ) * 100
ABS Percent = ABS ( [Percent] )
3. Then I create a Rank measure.
Rank = RANKX ( ALLSELECTED ( 'Table' ), [ABS Percent],, DESC, DENSE )
4. Create measure to show 1st, 2nd, 3nd highest value.
1st highest value = CALCULATE([ABS Percent],FILTER('Table',[Rank]=1))
2nd highest value = CALCULATE([ABS Percent],FILTER('Table',[Rank]=2))
3rd highest value = CALCULATE([ABS Percent],FILTER('Table',[Rank]=3))
For more details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thank you for your solution
it's professional work on your part
I have a problem if we want to add the date for example the rank does not change
for example :
calculate the 1st 2nd 3rd of the date Feb 15 ----> March 3
calculate the 1st 2nd 3rd of the date Jan 10 ----> Apr 1
it means when you choose a date range the rank changes according to date range
Hi @Anonymous ,
If you want the Rank value changed by the Date range you selected, the measure above can meet your requirement. Because I used "ALLSELECTED" in the expression.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Icey I am proud to be part of the Power Bi family
thank you it's too professional especially the aspect of sharing and help
I have a little problem with the rank when we have a null value it considers 0 as a 1st rank
how not to take null values
Mesures :
resultat :
Hi @Anonymous ,
If you want to show the records with null values, you can change your Measure like so:
Rank =
IF (
[ABS Percent] <> 0,-----------You can replace 0 with BLANK() in your scenario.
RANKX (
FILTER ( ALLSELECTED ( 'Table' ), [ABS Percent] <> 0 ),----You can replace 0 with BLANK() in your scenario.
[ABS Percent],
,
ASC,
DENSE
)
)
If you don't want to show the records with null value, just try something like below to filter the visual.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you sir we are too good for the first exercise
that's another one or I want the row to start with 1 but as you see in the picture it starts with 1 in a null value
thank you
Hi @Anonymous ,
If the screenshot below is like what you want, please try the expression above.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
62 |