cancel
Showing results for
Did you mean:
Helper I

## DAX: SubFigure logic to cater priority on date slicer first, then group by columns to sum qty

Hi friends,

I need the subtotal based on grouping included date filtering applied at first.

Priority: 1: date slicer filtering

2: sort by Ckey, ItemNo, DB_Ind, Entry No

3: group by Ckey, ItemNo

4: sum qty for inventory OUT only

But currently logic only able cater for item 2,3,4.

The first screen showing sample of raw data about overall inventory including inventory IN & inventory OUT.
inventory IN: Entry Type in {0,2,6}               inventory OUT: Entry Type in {1,3,5}

RawSample

Screen below is what currently I can get the correct result by applying below logic.

InventoryOUTQty =
SUMMARIZE(FILTER(V_ItemLedgerEntry,V_ItemLedgerEntry[EntryType] in {1,3,5}),"InventoryOUT",SUM(V_ItemLedgerEntry[InvoiceQty]))
Subtotal_InvOUTQty =
CALCULATE([InventoryOUTQty],ALLEXCEPT(V_ItemLedgerEntry,V_ItemLedgerEntry[CKey],V_ItemLedgerEntry[Item No_]))

Result1_Default

Btw, I cant get the result below if date slicer is selected, it still showing overall subtotal as -26 instead of -1.

Result_SelectionOnDateSlicer

Thank you.

XioFen

2 REPLIES 2
Super User

I am not sure I follow. You say you are getting the correct result. So that's good right?

It sort of sounds like a measures total issue to me but that's a complete guess. This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Learn Power BI 2nd Edition
Helper I

Hi Mr. Greg,

Yes, I am getting correct subfigure if no filter for date slicer (default showing all record). But if do some selection on date slicer, the subfigure will be wrong because it still showing grouping based on all record instead of record after applied date slicer filtering.

Thank you.

Xio Fen

Announcements