Forum Discussion

BenPo's avatar
BenPo
Frequent Visitor
5 years ago
Solved

Measure Totals Calculation

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 LabelsWE Inv.
Area 1150,000
  Region 130,000
    Plant12,500
       Prod 110,000
      Prod 21,000
      Prod 31500
   Plant 225,000
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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])

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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])

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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