The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hi, I am trying to get my code to count the number of dates that have a rank or 5 or anything below 5. I tried to do this with the code below but it doesn't seem to work, could any one help please?
(Heres an image of my dashboard; instead of counting the right number of dates at 5 or under , it counts every date)
Bottom 5 = VAR N = 5 RETURN COUNTX( SUMMARIZE ( 'Date Table','Date Table'[Year and Month],"total" , RANKX(all('Date Table'[Year and Month]), CALCULATE(SUM(Data[Value])),,ASC,Dense)) , IF([total]<=N,[total],BLANK()))
Solved! Go to Solution.
try like this:
Bottom Nth Value = COUNTROWS( FILTER( ALL( 'Date Table' ), RANKX( ALL( 'Date Table' ), [TotalValue], , DESC, Dense ) < 6 ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
For your measure to work you need to remove the filter from 'Year and Month' and from 'Date' such as below:
Bottom 5 = VAR N = 5 RETURN COUNTX ( SUMMARIZE ( 'Date Table', 'Date Table'[Year and Month], "total", RANKX ( ALL ( 'Date Table'[Year and Month], 'Date Table'[Date] ), CALCULATE ( SUM ( Data[Value] ) ), , ASC, DENSE ) ), IF ( [total] <= N, [total], BLANK () ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @Kote101
can you post the raw data?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Sure, here are some images of the tables i used
Hi @Kote101,
sorry but I can't work with a snapshot. Are you able to post data which can be copy/pasted?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Sorry i think this may help more;
https://filebin.net/qlgusdv2f3w0pfm4
you can access the pbix file i am working on there and the file i am using too.
try like this:
Bottom Nth Value = COUNTROWS( FILTER( ALL( 'Date Table' ), RANKX( ALL( 'Date Table' ), [TotalValue], , DESC, Dense ) < 6 ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Thanks @LivioLanzo, that worked thank you, do you have any idea why my original code was not working ?
For your measure to work you need to remove the filter from 'Year and Month' and from 'Date' such as below:
Bottom 5 = VAR N = 5 RETURN COUNTX ( SUMMARIZE ( 'Date Table', 'Date Table'[Year and Month], "total", RANKX ( ALL ( 'Date Table'[Year and Month], 'Date Table'[Date] ), CALCULATE ( SUM ( Data[Value] ) ), , ASC, DENSE ) ), IF ( [total] <= N, [total], BLANK () ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi,
If you want to add up the values form the Data Table appearing against the bottom 5 ranking Year/Month, then try this measure
=Measure = SUMX(FILTER(SUMMARIZE(VALUES('Date Table'[Year and Month]),[Year and Month],"ABCD",[Rank],"EFGH",SUM(Data[Value])),[ABCD]<=5),[EFGH])
The answer is 308K
Hope this helps.
Hi,
Try this
Measure = COUNTOWS(FILTER(SUMMARIZE(VALUES('Date Table'[Year and Month]),[Year and Month],"ABCD",[Rank],"EFGH",SUM(Data[Value])),[ABCD]<=5))
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
158 | |
109 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |