Filtering within a measure or column

[jasonwq]

Hi. I was wondering if I can filter within a measure or a column without affecting the entire visual?

ENTRY_TYPE	QUANTITY
Sale	-8
Purchase	50
Consumption	-15
Output	13

In the above table, let's say I'd like to essentially end up with something like this:

ENTRY_TYPE	QUANTITY	SALES	PURCHASE	CONSUMPTION	OUTPUT
Sale	-8	-8	0	0	0
Purchase	50	0	50	0	0
Consumption	-15	0	0	-15	0
Output	13	0	0	0	13

That way I could retrieve my SUM(table[SALES]) without Filtering the entire visual by ENTRY_TYPE by SALES.

Is that possible?

[amitchandak]

@jasonwq , You can create one more table with distinct value ENTRY_TYPE and join it with ENTRY_TYPE. And then use both ENTRY_TYPE on matrix. And use sum Quantity, Grand total should show what you have in Quantity in second visual

[jasonwq]

@amitchandak 

I used

New Column = if(ItemLedgerEntries[Entry_Type] = "Sale", ItemLedgerEntries[Quantity])

Thanks for your help but I don't know how to utilize NewTable. 

[v-rzhou-msft · Accepted solution]

Hi @jasonwq 

I think you currently build four calculated columns as below.

Sales,Purchase Consumption and Output's Format looks the same.

 ... = if(ItemLedgerEntries[Entry_Type] = "...", ItemLedgerEntries[Quantity])

In addition to  amitchandak, here I will show you how to build a new table to achieve your goal.

Build a new table by dax code.

DIM_ENTRY_TYPE = VALUES('Table'[ENTRY_TYPE])

Build a relationship between two tables.

Build a measure.

SUM QTY = SUM('Table'[QUANTITY])+0

Matrix is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jasonwq]

But then the data will not update with my system, correct? The new table will not be a Live database.