Forum Discussion
Top N table using dates from calendar table
Pls consider following tables and relationships:
I want to make a visual showing the Top 10 sales amount per salesrep (from table A) per date, with date using the calendar table so I can aggregate in other time intervals.
I can get this to work with the date-with-unusable-formatting-from-table-A using the following formula:
Rank Sales per salesrep per day = RANKX(ALLSELECTED('Table A'[Name salesrep], 'Table A'[Date in unusable format]), CALCULATE(SUM('Table A'[Amount])))
However, if I make the table and substitute 'Table A'[Date in unusable format] with Calendar[Date] I get output that is gibberish. It works when I make any other visualization (no top N, and through the TransactionID relationship with table B it finds the right date in calendar and I can aggregate Amount by week, for example, without any problems), but not when I use RANKX and TopN filter.
So... how do I make a Top N using 'Table A'[Name salesrep], 'Table A'[Amount] and Calendar[Date]? I wish to solve this with DAX, not by adding another column to Table A (data set is big enough as it is).
I can get it to work without adding a column in power query and having to create a rule to transform the unreadable date in Table A to a date that is similar to Calendar[Date] by adding a column in DAX using:
column: Table A Proper transaction Date = RELATED(Calendar[Date])
It figures out the correct corresponding date from the calendar table into Table A, presumably through the Transaction ID relation it has with table B (the table that has the 'primary' relationship with the calendar table).
Then adjust the RankX formula to following:
Rank Sales per salesrep per day = RANKX(ALLSELECTED('Table A'[Name salesrep], 'Table A'[Table A Proper transaction Date]), CALCULATE(SUM('Table A'[Amount])))Is there a cleaner way to do this or did I manage to answer my own question?
2 Replies
I can get it to work without adding a column in power query and having to create a rule to transform the unreadable date in Table A to a date that is similar to Calendar[Date] by adding a column in DAX using:
column: Table A Proper transaction Date = RELATED(Calendar[Date])
It figures out the correct corresponding date from the calendar table into Table A, presumably through the Transaction ID relation it has with table B (the table that has the 'primary' relationship with the calendar table).
Then adjust the RankX formula to following:
Rank Sales per salesrep per day = RANKX(ALLSELECTED('Table A'[Name salesrep], 'Table A'[Table A Proper transaction Date]), CALCULATE(SUM('Table A'[Amount])))Is there a cleaner way to do this or did I manage to answer my own question?