cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
LizBethA Frequent Visitor
Frequent Visitor

DAX - Running Total and Visualization Help

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

 

 

CountryRegionAreaValue to Rank Amount Rank X Calculated (Dense) **bleep** Amout Calculated  Expected Rank  Expected **bleep** Amount 
BDA-12-5             170.001 1                        170
CDA-22              100.00210802                        270
DIA-30  210803                        270
ACA-00$0.00 210804                        270
ACA-01$0.00             200.00210805                        470
AIA-04$0.00             300.00210806                        770
BDA-10$0.00             200.00210807                        970
CIA-20$0.00             100.00210808                    1,070
C A-23$0.00 210809                    1,070
CCA-25$0.00                10.002108010                    1,080
AIA-02$2.00                10.0011119011                    1,090
B A-13$2.00             100.0011119012                    1,190
CDA-26$4.00                50.0013                        1,24013                    1,240
ADA-05$5.00                40.0014                        1,28014                    1,280
BDA-11$6.00                20.0015                        1,30015                    1,300
C A-27$7.00                10.0016                        1,31016                    1,310
AIA-03$8.00                70.0017                        1,38017                    1,380
B A-14$9.00                50.0018                        1,43018                    1,430
CIA-24$10.00                30.0019                        1,46019                    1,460
ADA-06$11.00                20.0020                        1,48020                    1,480
B A-15$12.00             200.0021                        1,68021                    1,680
CCA-21$20.00             400.0022                        2,08022                    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.

 

2019-04-04_8-23-04.png

 

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!

 

 

2 REPLIES 2
Community Support Team
Community Support Team

Re: DAX - Running Total and Visualization Help

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

9.png

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

10.png

 

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.

Community Support Team
Community Support Team

Re: DAX - Running Total and Visualization Help

Hi @LizBethA 

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

 

Best Regards

Maggie