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 ,
I need help plz provide the solution.
Below is a pseudo data for understanding and currently working in Direct query .
i have line and stacked bar chart .
in x-axis I have 2 dimension Product and checks. Checks contains T,F,N values.
In columns y axis I have a calculated measure sales and Legend is category columns.
I have several filters and paramters applied.
I am trying to create a rank function ,
code: RANKS(
SUMMARIZE( ALLSELECTED( 'Table'), 'Table'[Product],'Table[Checks],'Table'[Category]),
[Sales], ,DESC,DENSE)
but for 2 product i am getting same rank and but 2 product didn't have the same values .
Please help in these.
Solved! Go to Solution.
Hi @Nobie
You can refer to the following measure
Measure = var a=SUMMARIZE(ALLSELECTED('Table'),[Product],"Sum",SUM('Table'[Sales]))
var b=ADDCOLUMNS(a,"Rank",RANKX(a,[Sum],,DESC,Dense))
return MAXX(FILTER(b,[Product] in VALUES('Table'[Product])),[Rank])
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Nobie
You can refer to the following measure
Measure = var a=SUMMARIZE(ALLSELECTED('Table'),[Product],"Sum",SUM('Table'[Sales]))
var b=ADDCOLUMNS(a,"Rank",RANKX(a,[Sum],,DESC,Dense))
return MAXX(FILTER(b,[Product] in VALUES('Table'[Product])),[Rank])
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Can u plz tell me what does statement after return doing?
can you provide some sample data with your expected solution?
Proud to be a Super User!
i have attached a excel .
basically i want the rank on the basis on Product on basis on sales and line and checks to be ignored .
Hi thanks, but where is the attachment? I need the data in text format to do this.
Proud to be a Super User!
this is the table .
Product | Line | Checks | Sales |
Product A | L1 | N | 2601 |
Product A | L1 | N | |
Product A | L1 | T | 23 |
Product A | L2 | N | |
ProductB | L1 | N | 538 |
ProductB | l1 | T | 130 |
ProductB | L2 | N | |
Product C | L1 | N | 277 |
Product C | L1 | T | 15 |
Product C | L2 | N |
this is the output table
Product | Line | Checks | Sales | RANKS |
Product A | L1 | N | 2601 | 1 |
Product A | L1 | N | 1 | |
Product A | L1 | T | 23 | 1 |
Product A | L2 | N | 1 | |
ProductB | L1 | N | 538 | 2 |
ProductB | l1 | T | 130 | 2 |
ProductB | L2 | N | 2 | |
Product C | L1 | N | 277 | 3 |
Product C | L1 | T | 15 | 3 |
Product C | L2 | N | 3 |
below is the summary
Product | sales | Ranks |
Product A | 2624 | 1 |
Product B | 668 | 2 |
Product C | 292 | 3 |
hi see attached. you will need a product table for this solution that needs to be joined to your table.
Product Rank =
IF (
ISINSCOPE ( 'Product'[Product] ),
RANKX ( ALLSELECTED ( 'Product'[Product] ), CALCULATE ( [total sales] ) )
)
the inscope is to prevent the rank from showing at the total level
Proud to be a Super User!
Hi, Thanks for the help that didn't work
this didn't worked. I am attaching google drive link excel
when you said it didn't work, please make sure you explain what it is your are expecting so we on the same page. I am assuming you mean its saying rank 1,3 and you wanting rank 1,2.
all you need to do is change your rank to allselected rather than all and then it will only use the selected values. Otherwise it will scan all your data regardless of what you are selecting.
Proud to be a Super User!
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 |
---|---|
112 | |
100 | |
76 | |
64 | |
63 |
User | Count |
---|---|
142 | |
105 | |
102 | |
80 | |
67 |