Forum Discussion
Looking Up Correct Value
Hello! I am hoping for some assistance with this because I'm not really sure where to start. I have several tables that come from excel that I am trying to use in PowerBI. In PowerBI I have 3 slicers: Date (year/month), Category, Location.
I have a table from excel that has monthly counts for locations & categories. For example:
| 1/1/24 | 2/1/24 | 3/1/24 | |
| Location 1 | 35 (sum of 3 rows below) | 18 (sum of 3 rows below) | 25 (sum of 3 rows below) |
| Location 1 Category A | 10 | 8 | 9 |
| Location 1 Category B | 12 | 6 | 12 |
| Location 1 Category C | 13 | 4 | 4 |
| Location 2 | (sum of 3 rows below) | (sum of 3 rows below) | (sum of 3 rows below) |
| Location 2 Category A | |||
| Location 2 Category B | |||
| Location 2 Category C | |||
| Etc... |
I want to be able to use the 3 slicers to find the value of any combination of the slicer - so as broad as all values, and as narrow as a specific month, specific category, and specific location).
I also have a table called date - which has all of the date values in it.
The categories and locations also are both columns in a separate table called data. To make it more difficult, the categories and locations change based on time period selected.
I'm just not sure what I should use to be able to look this up so closely, particularly with the slicers.
Thanks!
you can do a summerize table, its like a group by. look the exemple below.
original tableand this is the summarize table, i group by location and date, and sum values, but you can change the fields.
3 Replies
you need change you table like below, and use a calculate mensure to sum location 1, location 2, category a, category b, etc...
LeandroDeodato Thanks for the quick response. Would I have to keep adding to the calculated measure every month as new data gets added? Is there a way for that to be done automatically?
you can do a summerize table, its like a group by. look the exemple below.
original tableand this is the summarize table, i group by location and date, and sum values, but you can change the fields.
