Forum Discussion

jmerencilla's avatar
jmerencilla
Icon for Helper I rankHelper I
4 years ago
Solved

Getting the Bucket Totals

Hello! I am comparing data from 2 periods by category and I managed to create measures and they look like this.

Based on the values of P1 Sales and P2 Sales, the difference is put in the correct bucket (Higher, Lower, Lost, New)

However, at the Total Level, the total values of P1 and P2 sales are compared instead of the individual bucket totals, making all buckets empty except for Higher. How do I get the individual bucket totals?

 

 

Higher = 
if([P2 Sales] > [P1 Sales] && not ISBLANK([P1 Sales])
    , [P2 Sales] - [P1 Sales]
    , BLANK()
)

 

 

 

 

Lower Sales = 
if([P1 Sales] > [P2 Sales] && not ISBLANK([P2 Sales])
    , [P2 Sales] - [P1 Sales]
    , BLANK()
)<p> </p>

 

 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    jmerencilla , Based on what I got, you have to force sume from visual row level

     

    sumx(summarize(Table, Table[Visual column]),calculate(if([P2 Sales] > [P1 Sales] && not ISBLANK([P1 Sales])
    , [P2 Sales] - [P1 Sales]
    , BLANK()
    )) )

3 Replies

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

    jmerencilla , Based on what I got, you have to force sume from visual row level

     

    sumx(summarize(Table, Table[Visual column]),calculate(if([P2 Sales] > [P1 Sales] && not ISBLANK([P1 Sales])
    , [P2 Sales] - [P1 Sales]
    , BLANK()
    )) )

  • jmerencilla's avatar
    jmerencilla
    Icon for Helper I rankHelper I
    4 years ago

    amitchandak , Thank you for the solution. It works perfectly as expected. But for some reason, the same DAX does not work in the Lost column. Now everything in Lost column is blank. 

    It should just be the opposite of the New column 

    New = 
SUMX(
    SUMMARIZE(
        'Sales'
        , 'Sales'[Category]
    )
    , CALCULATE(
        if([P2 Sales] > 0 && ISBLANK([P1 Sales])
            , [P2 Sales] 
            , BLANK()
        )
    ) 
    Lost = 
SUMX(
    SUMMARIZE(
        'Sales'
        , 'Sales'[Category]
    )
    , CALCULATE(
        if([P1 Sales] > 0 && ISBLANK([P2 Sales])
            , -[P1 Sales]
            , BLANK()
        )
    ) 
)

    Appreciate it if you could spare a minute to look into this.

    • jmerencilla's avatar
      jmerencilla
      Icon for Helper I rankHelper I
      4 years ago

      I tried to simply display the P1 Sales and I got this weird result:

      YTD Lost Sales = 
SUMX(
    SUMMARIZE(
        'Sales'
        , 'Sales'[Category]
    )
    , CALCULATE([P1 Sales]) 
)

      Notice that those categories with P2 sales, do not show any values at all when it should. But with the opposite logic for New Sales, it's displaying the value correctly.

      Appreciate any input you guys can provide me. Thanks!