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

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.

jmerencilla_1-1660097624832.png

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>

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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()
)) )

View solution in original post

3 REPLIES 3
jmerencilla
Frequent Visitor

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

jmerencilla_0-1660113274060.png

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.

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

jmerencilla_1-1660120988480.png

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!

 

amitchandak
Super User
Super User

@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()
)) )

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.