CODEWITHSUNDEEP
powerbidaxmeasure
aarti
aarti

Reputation: 1

Calculating measure at a certain grain in Power BI

I have a power BI report with dataset (week, business unit, SKU, Inbound, Outbound). I have below measures:

Subtract = Inbounds- outbounds

Non Ngetiave MEAS1 = IF (inbounds-outbounds)<0,0,(inbounds-outbounds)

While calculating Non Ngetiave MEAS1, I want to show the "overall" value to be the sum of individual Non Ngetiave MEAS1 calculations at sku-week grain. If you look at the PBI, currently it shows 1683, what I would want is 2173 which is the sum of individual Non Ngetiave MEAS1.

It works when I am using a calculated column. I want it to work with measures to because in my actual data set, all these are measures.

NonNegMeas1 = 
    SUMX(
        VALUES('Table'[SKU] ),
        CALCULATE(
            CALCULATE( 
                 if('Table'[Subtract]<0,0,'Table'[Subtract]),
                 ALL('Table'[SKU]),
                 SUMMARIZE('Table', 'Table'[SKU])
             )
        )
    )

PBI LINK

Upvotes: 0

Views: 492

Answers (1)

Pavel Klammert
Pavel Klammert

Reputation: 315

I'm not sure if this is exactly what you wanted, but:

solution = SUMX(
SUMMARIZE(
    ('Table'),'Table'[SKU],'Table'[Week] ),
     if('Table'[Subtract]<0,0,'Table'[Subtract])
    )

The only change is the additional group by column (week) going into the summarize function, which changes the calculation. Without the C SKU has Outbound - inbound < 0, therefore it seems, that the 1683 result is actually correct. Please, let me know if that solves your issue. Thanks

Upvotes: 0

Related Questions