Forum Discussion
Helper IRankX Dense
Hi 😧
I made a few changes. Brought brokers full name into your brokers table. Changed Date Table to mark as Date Table. Put new measures:
Total Sales
YTD Sales
Rank Brokers
I think instead of using implicit measures your better off using a measure. E.G. Total Revenue is implict. Total Sales is the measure to use. Also bring brokers name in your tables from brokers table. I don't think you need to show month sales like present. Use Month field from Dte table for all visuals. I hope this helps! I will send link in pers email.
Rank Brokers(YTD Sales ) = RANKX(ALL('Broker Table'[Full Name]), [YTD Sales],,DESC)Bill
Helper IThanks again for the response! I attempted to change the cardinality, however Power BI desktop said that none of the other options were valid for this relationship (it's matching a primary & foriegn key , Person_Key; from our DB).
Attempted to use the DAX you provided. It did not work; below is the code after adjusting some of the column names & the error PBI desktop returned:
Rank2 =
VAR __table = FILTER ( ALL ( 'Broker Totals' ), [running total] > EARLIER ('Broker Totals' [running total] ))
VAR __tableSame = FILTER ( ALL ('Broker Totals' ), [running total] = EARLIER ('Broker Totals' [running total] ) && [Broker Name] < EARLIER ('Broker Table'[Broker Name] ))
RETURN
COUNTROWS ( __table ) + 1 + COUNTROWS ( __tableSame )
!Error!EARLIER/EARLIEST refers to an earlier row context which doesn't exist.
Thanks again for the continued support, still kind of new to this & trying to learn!
Solution SageHi:
N.P. I beleive to answer the question I would need to see some data in the set up you are using.
So much depends on the tables, relationships and the context.
I'm happy to help but would need something to work with.
Thanks..
- Whitewater100
Hi 😧
I made a few changes. Brought brokers full name into your brokers table. Changed Date Table to mark as Date Table. Put new measures:
Total Sales
YTD Sales
Rank Brokers
I think instead of using implicit measures your better off using a measure. E.G. Total Revenue is implict. Total Sales is the measure to use. Also bring brokers name in your tables from brokers table. I don't think you need to show month sales like present. Use Month field from Dte table for all visuals. I hope this helps! I will send link in pers email.
Rank Brokers(YTD Sales ) = RANKX(ALL('Broker Table'[Full Name]), [YTD Sales],,DESC)Bill
- DeanUW
Hello,
I would appreciate more help with this rank issue. How would i go about sharing the pbix with you?
- Whitewater100
Good morning:
You can either post here as link or if you want,my email [email protected]. Sorry for the delay!
- DeanUW
Hi,
Thank you for the adjustments you made. When I attempted to add the Rank measure you created on to the remaining pages that list out all 450+ brokers; the rank broke again, it ranked everyone 1. I will attempt to make the month column adjustments you recommended & see if that makes a difference.
- Whitewater100
Hi:
Yes, the adjustments should help with the measure. I noticed your fact table was joined on "pending" date, so that date is driving the results. I also saw the YTD and Total Sales measure yield same number. Hopefully that sounds correct to you.
Good luck!
- Whitewater100
Hi:
If you don't want the ranking to apply to totals or anywhere besides next to Broker[Full Name] you can use this for rank. Just a minor tweak.
Rank Brokers(YTD Sales ) = If(HASONEVALUE('Broker Table'[Full Name]),RANKX(ALL('Broker Table'[Full Name]), [YTD Sales],,DESC)) - DeanUW
Good Morning,
Your original DAX measures that you made on my file yesterday were the correct solution. I did have to rebuild the entire report based on you feedback of using the dates incorrectly. Once I rebuilt the 'Sales Totals' table to leverage the 'Date Table' more effectively your YTD & Total Sales DAX measures worked perfectly. Thank you again for the different perspective on how to build it. Have a great weekend!
- Whitewater100
Great and you are welcome!