Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.