Forum Discussion
Temp Table
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.
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.
6 Replies
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.
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.
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
