Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sum on a dynamic range

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!

Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks so much, but it doesn't work

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous Hello, could you help pls?

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    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.