Sum on a dynamic range

[Anonymous]

Hi PB expert, 

It's prior to my previous post Dynamic Axis by selected percentage range 

Please download the pbix here 

I have a measure that can control the line chart, the line chart it will vary by the % range select,

Measure = 
VAR _countC =
    CALCULATE ( DISTINCTCOUNT ( 'Sheet1'[CustomerID] ), ALL ( 'Sheet1' ) )
VAR _percent =
    ROUND ( DIVIDE ( 1, _countC ) * [Rank], 2 ) * 100
RETURN
    IF (
        _percent >= MIN ( 'Axis'[Axis] )
            && _percent <= MAX ( 'Axis'[Axis] ),
        SUM ( Sheet1[Sales] ),
        BLANK ()
    )

However, the SUM is not what I expect

In the graph shown, the expected result = 65000.00,

Can any one help? many thanks!

[Icey · Accepted solution]

Hi @Anonymous ,

Try to create your Rank measure as a column.

Rank Column = 
RANKX (
    ALL ( Sheet1[CustomerID] ),
    CALCULATE ( SUM ( Sheet1[Sales] ), ALLEXCEPT ( Sheet1, Sheet1[CustomerID] ) ),
    ,
    DESC
)

Then, replace "[Rank]" in your [Measure] with "MAX ( Sheet1[Rank Column] )".

Measure = 
VAR _countC =
    CALCULATE ( DISTINCTCOUNT ( 'Sheet1'[CustomerID] ), ALL ( 'Sheet1' ) )
VAR _percent =
    ROUND ( DIVIDE ( 1, _countC ) * MAX ( Sheet1[Rank Column] ), 2 ) * 100
RETURN
    IF (
        _percent >= MIN ( 'Axis'[Axis] )
            && _percent <= MAX ( 'Axis'[Axis] ),
        SUM ( Sheet1[Sales] ),
        BLANK ()
    )

Then, create another measure to get the total value:

Measure 2 = SUMX( VALUES( Sheet1[CustomerID] ) , [Measure] )

BTW, .pbix file attached.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-yiruan-msft Hello, could you help pls?

[amitchandak]

@Anonymous , Try like

Measure = 
VAR _countC =
    CALCULATE ( DISTINCTCOUNT ( 'Sheet1'[CustomerID] ), ALL ( 'Sheet1' ) )
VAR _percent =
    ROUND ( DIVIDE ( 1, _countC ) * [Rank], 2 ) * 100
RETURN
    sumx(values('Sheet1'[CustomerID]), IF (
        _percent >= MIN ( 'Axis'[Axis] )
            && _percent <= MAX ( 'Axis'[Axis] ),
        SUM ( Sheet1[Sales] ),
        BLANK ()
    ))

[Anonymous]

Thanks so much, but it doesn't work