DAX - Running Total and Visualization Help

[LizBethA]

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
B	D	A-12	-5	170.00	1		1	170
C	D	A-22		100.00	2	1080	2	270
D	I	A-30			2	1080	3	270
A	C	A-00	$0.00		2	1080	4	270
A	C	A-01	$0.00	200.00	2	1080	5	470
A	I	A-04	$0.00	300.00	2	1080	6	770
B	D	A-10	$0.00	200.00	2	1080	7	970
C	I	A-20	$0.00	100.00	2	1080	8	1,070
C		A-23	$0.00		2	1080	9	1,070
C	C	A-25	$0.00	10.00	2	1080	10	1,080
A	I	A-02	$2.00	10.00	11	1190	11	1,090
B		A-13	$2.00	100.00	11	1190	12	1,190
C	D	A-26	$4.00	50.00	13	1,240	13	1,240
A	D	A-05	$5.00	40.00	14	1,280	14	1,280
B	D	A-11	$6.00	20.00	15	1,300	15	1,300
C		A-27	$7.00	10.00	16	1,310	16	1,310
A	I	A-03	$8.00	70.00	17	1,380	17	1,380
B		A-14	$9.00	50.00	18	1,430	18	1,430
C	I	A-24	$10.00	30.00	19	1,460	19	1,460
A	D	A-06	$11.00	20.00	20	1,480	20	1,480
B		A-15	$12.00	200.00	21	1,680	21	1,680
C	C	A-21	$20.00	400.00	22	2,080	22	2,080

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])

Cumulative Remaining Hydrocarbon Volume (Bcfe) =

CALCULATE ([Total Amount],

    FILTER (

        ALLSELECTED('My Table'),

        'My Table'[Value to Rank] <= MAX ('My Table'[Value to Rank])

    ))

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.  

Thanks!

[v-juanli-msft]

Hi @LizBethA 

In order to get more help from others, you'd better to cut short many problems into several posts.

Best Regards

Maggie

[v-juanli-msft]

Hi @LizBethA 

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

file-close&&apply

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

Best Regards

Maggie

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.