Temp Table

[CetinT]

Hi,

I want to crate a temp table for my calculation. You can find smaple data below. When I filter "MONTH" I want to see all "ITEM" names but I want to see only total of buyed items "QTY" and "AMOUNT" in that month. I already tried CALCULATEDTABLE but I could't make it? Could you help me?

SAMPLE DATA

MONTH	ITEM NAME	QTY	AMOUNT
Jan	ITEM1	10	100
Jan	ITEM1	11	111
Jan	ITEM2	11	55
Jan	ITEM3	5	30
Jan	ITEM4	6	48
Jan	ITEM4	10	81
Jan	ITEM6	11	99
Jan	ITEM7	20	60
Feb	ITEM1	5	55
Feb	ITEM2	40	220
Feb	ITEM3	12	79,2
Feb	ITEM4	20	176
Feb	ITEM4	20	180
Feb	ITEM5	25	250
Feb	ITEM7	11	36,3
Mar	ITEM1	10	115
Mar	ITEM2	4	24
Mar	ITEM3	7	49
Mar	ITEM3	10	71
Mar	ITEM4	6	54
Mar	ITEM5	3	33
Mar	ITEM6	1	10

What I want to see? (forexample when I filter "Jan")

ITEM NAME	TOT. QTY	TOT. AMOUNT
ITEM1	21	211
ITEM2	11	55
ITEM3	5	30
ITEM4	16	129
ITEM5	0	0
ITEM6	11	99
ITEM7	20	60

Thanks.

[davehus · Accepted solution]

Hi @CetinT ,

See if the attached example will help. Bare in mind that you will need to change the month in the temp table as this is calculated in memory and not on the fly. I've created a table called summarized using and then another called summarized product. I've used lookupvalue so that you get the null values you need also.

HTH

Did I help you today? Please accept my solution and hit the Kudos button.

[CetinT]

Thank you @davehus . Good solution. 

[davehus]

Very Welcome. 🙂

[CetinT]

Hi @davehus ,

I have another question about this 🙄. If I want to make virtual table in memory or on the fly (your description) Ho can I do it?

[davehus]

Hi @CetinT ,

You can achieve this by adding the month to the report canvas as a slicer and then adding the Item and values to a matrix table. There is no calculatetable required.

This link will show you how to display items with not data in the matrix.

https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data

Hope this helps

Did I help you today? Please accept my solution and hit the Kudos button.

[CetinT]

Hi davehus,

Thank you for your message. But I don't want show tables in to report. Actually I want to calculate inflation rate between two selected months. That's why I want to create two different table and make calculation between to temp tables.

Regards