Table or Matrix Visual with Subcategory Only Applying to One Main Category

[AlvinLy]

Hello Fabric Community,

I have what I believe is a niche problem and I wanted to ask the community if what I need to produce is currently possible. Please see link to the data before continue reading, specifically "Drill Down Problem" tab. Note all the data was fabricated but I made it similar to my current situation. https://drive.google.com/file/d/1ITIrPYp-MI85XORY_XIb7D_s2gCGT7Tv/view?usp=drivesdk

What I need is a table OR matrix visual which first shows all the Location as the main rows and then for "Local" location to show the subcategories ("City") and then on the columns of the visual the sum of sales of each color. Kind of like this
Local
     City A
     City B
     City C

Asian
Africa

Europe
National

Is this type of visual currently possible in power BI

[AlvinLy · Accepted solution]

Hello,

I have devised two solutions both of them aren't perfectly what I want but they both serve the purpose and I hope this can help others. There are two approaches:
1. Drill Through

2. Customizing the calculation

In the first method I simply placed a drill through button and a drill through page that only expanded on the city for local sales. I also limited the drill through button to only work if "Local" was chosen on the table and if nothing or anything else was chosen the button would be disabled

The second option is to alter my calculation and add a SELECTEDVALUE(City_D[City Name]) and if that appears blank then the calculation will also result in a blank value (embedded in my switch statement)

Either of these option gets me to present the data I want to but in two different ways

[Wilson_]

Hi AlvinLy,

Thankfully, I don't believe your problem is niche at all. However, it looks like your issue is there is no way in your model currently to know what city belongs to what Location. My first instinct is they should be in the same dimension table. I also don't think Project ID should be in your dimension tables; that is already in your sales fact table where it belongs.

EDIT: I see now that you have a Location ID and City ID in your fact table. It would be easier (and more intuitive!) if your "locations" were in the city table and not their own separate table. That detail should not be stored in your fact table, generally speaking.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[AlvinLy]

Hi Wilson,

Thanks for looking at my data set so far. I understand as i was fabricating the data that it did not make complete sense, but the dimensions "location", "city", etc. are all alternatives that i made up compared to my real data, so instinctively it does not make sense, but this is a subset of a much larger dataset with many more fact and dimension tables where a snowflake schema may not be as robust as a plain star schem for my current situation

Thanks for going through the data though! Hopefully there is some function that does this or alternatively possibly some measures used as pseudo column in my table as calcs