Measure Totals Calculation

[BenPo]

Hello!

Baseline calculation returns inventory amount for Friday if Saturday is 0/blank.

if(CALCULATE(sumx(InventoryTable,InventoryTable[CountTotal]),filter(Date,Date[FiscalDayOfWeek]=7))=0,

CALCULATE(sumx(InventoryTable,InventoryTable[CountTotal]),filter(Date,Date[FiscalDayOfWeek]=6)),

CALCULATE(sumx(InventoryTable,InventoryTable[CountTotal]),filter(Date,Date[FiscalDayOfWeek]=7)))

When I drop this in a matrix with a location hierarchy (Area, Region, Plant) + product the totals don't add up correctly. I've read and tried solutions using "isfiltered" and "summarize" but I don't understand how to use those in case.

 Example of Matrix

Row Labels	WE Inv.
Area 1	150,000
Region 1	30,000
Plant	12,500
Prod 1	10,000
Prod 2	1,000
Prod 3	1500
Plant 2	25,000

[BenPo]

Thank you, amitchandak!

[v-yiruan-msft]

Hi @BenPo ,

Please create another new measure as below base on your original measure and check whether it can work in your scenario:

SUMX (
    GROUPBY (
        InventoryTable,
        'InventoryTable'[Area],
        'InventoryTable'[Region],
        'InventoryTable'[Plant]
    ),
    [Yourmeasure]
)

If the above one is not working, you can also refer the method in the following links to get it. Or you can provide some sample data in your InventoryTable and the formula of measure [CountTotal], later we will check and provide the solution.

Clever Hierarchy Handling in DAX

Use IsInScope to get the right hierarchy level in DAX

Best Regards

[amitchandak · Accepted solution]

@BenPo , You have to try like

Inv =if(CALCULATE(sumx(InventoryTable,InventoryTable[CountTotal]),filter(Date,Date[FiscalDayOfWeek]=7))=0,
CALCULATE(sumx(InventoryTable,InventoryTable[CountTotal]),filter(Date,Date[FiscalDayOfWeek]=6)),
CALCULATE(sumx(InventoryTable,InventoryTable[CountTotal]),filter(Date,Date[FiscalDayOfWeek]=7)))

New Inv = sumx(summarize(Table, [Region], [area], [plant], [product], "_1",[Inv]),[_1])