I have a user that is wanting a scatter plot (actuall a stair step line chart, but that's another issue I'll try to deal with later) that has a value (that he wants the cumulative amount calculated on) on the X-Axis, and a **bleep** of an Amount on the Y Axis.
In order to get the **bleep** value in the correct order, I created a New column using DAX (I get an error when trying to calculate a Measure) using RANKX. Ideally I'd like the values ranked individually, but all values that are alike are given the same rank. I wouldn't mind ranking on multiple columns (Value to Rank and Area, for example), but haven't found a way to do this (see Rank X Calculated vs Expected Rank).
|Country||Region||Area||Value to Rank||Amount||Rank X Calculated (Dense)||**bleep** Amout Calculated||Expected Rank||Expected **bleep** Amount|
I then create a measure that calculates the **bleep** of the amount. Note how the **bleep** amount sums all the items ranked individually rather than by the individual rows.
however, there is only one datapoint for the ranks that are the same
Scatter plot Setup:
X Axis: **bleep** Amount Calculated
Y Axis: Value to Rank
Detail: Area (I also tried the RankX Calculated (Dense) but it didn't make a difference).
Filtering/slicing works nice and the Cumulative amount is recalculated. Amounts are correct, and the line shape is updated as expected and accurate. Except for one dot rather than unique dots based of the detail, it provides what the customer is looking for.
Am I making this too difficult.
How can I get a Rank/Index to show unique Index values?
Can I calculate a Rank/Index on multiple columns?
What approach would I need to take to calculate a **bleep** based on the index as well as the Region, for example?
Here is the DAX Statement I used for calculating the **bleep** Amount:
Total Amount = sum('My Table'[Amount])
Thanks, any advice or if links to references that may help will be appreciated.
BTW, The next request if to combine data from 2 different tables and plot 2 different set of **bleep** values (based off the data set) on one chart.
It seems you create your "Rank X Calculated (Dense)" with the formula below
rank1 = RANKX(Sheet3,Sheet3[Value to Rank],,ASC,Skip)
To get expected rank, i go to Edit queries,
firstly sort "Value to Rank" on ascending order, secondly, sort "Country" on ascending order,
Then add an index column from1
Create calcuated columns
rank1 = RANKX(Sheet3,Sheet3[Value to Rank],,ASC,Skip) rank2 = VAR countd = CALCULATE ( COUNT ( Sheet3[rank1] ), FILTER ( Sheet3, Sheet3[rank1] = EARLIER ( Sheet3[rank1] ) ) ) VAR rankbyrank = RANKX ( FILTER ( Sheet3, Sheet3[rank1] = EARLIER ( Sheet3[rank1] ) ), [Index], , ASC, DENSE ) VAR rank2 = IF ( countd > 1, rankbyrank - 1, 0 ) RETURN [rank1] + rank2
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.