Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hey all, I already checked a lot of the solved "rankx" questions, but I didn't see anything that dealt with something as potentially simple as mine. Hopefully that'll make it easy to fix.
In a [Sales] table, I have [product units sold] out of [total sold], giving me a [% of total], which all looks correct. However, my rankx:
Sales Units Rank = rankx(all(Sales), Sales[% Sales Units])
gives weird values sometimes (see picture). In the end, my running total is:
Sales Unit Running Total = CALCULATE ( SUM ( Sales[% Sales Units] ), ALL ( Sales ), Sales[Sales Units Rank] <= EARLIER (Sales[Sales Units Rank] ) )
Any tips?
Hi RMDNA,
Based on your description, there’re three original columns [Sales Units], [Sales Units Total] and [% Sales Units] in your table, right?
To calculate running total which can meet your requirement, you can modify DAX formula like below:
Sales Unit Running Total =
CALCULATE (
SUM ( Sales[% Sales Units] ),
ALL ( Sales ),
Sales[Sales Units Rank] <= EARLIER (Sales[Sales Units Rank] ),
Sales[Sales Units] = EARLIER(Sales[Sales Units Rank]),
Sales[Sales Unit Total] = EARLIER(Sales[Sales Unit Total])
)
Regards,
Jimmy Tao
'Sales Units' comes directly from the table, while 'Sales Unit Total' and '% Sales Units' are either calculated columns or measures (I've tried both).
When I try any of the running total examples, I get the following:
"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
Try converting these to MEASURE...perhaps
Sales Units Rank = RANKX ( ALLSELECTED ( Sales ), CALCULATE ( SUM ( Sales[% Sales Units] ) ) )
Sales Unit Running Total = VAR myrank = [Sales Units Rank] RETURN CALCULATE ( SUM ( Sales[% Sales Units] ), ALLSELECTED ( Sales ), [Sales Units Rank] <= myrank )
Using your rank code as a measure [SUR] gives the following, which will still throw off the running total. It looks better, but something is still off.
User | Count |
---|---|
96 | |
85 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |