Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi everyone,
I want to use matrices to display customer sales by year and by product categories. The problem is that my customer names are quite long, so using two different tables takes up a lot of real estate in my report. Furthermore, it's hard to compare the numbers side-by-side since the user has to scroll in both tables and since word wrapping results in differences in row height.
The best alternative would by to combine the two tables into the same table/matrix (see example below). I have, however, not found a solution that produces the desired effect. Does anyone know a way to achieve this? Maybe through a custom visual?
OBS: I am working with a live connection to a database and can't make changes to the data model, aside from adding measures.
Customer Sales by Year (Matrix A)
Customer Name | 2018 | 2019 | 2020 |
aaaaaaaaaaaaaaaaaaaaaaaaaa1 | xxx | xxx | xxx |
aaaaaaaaaaaaaaaaaaaaaaaaaa2 | xxx | xxx | xxx |
aaaaaaaaaaaaaaaaaaaaaaaaaa3 | xxx | xxx | xxx |
Customer Sales by Product (Matrix A)
Customer Name | Product A | Product B | Product C |
aaaaaaaaaaaaaaaaaaaaaaaaaa1 | xxx | xxx | xxx |
aaaaaaaaaaaaaaaaaaaaaaaaaa2 | xxx | xxx | xxx |
aaaaaaaaaaaaaaaaaaaaaaaaaa3 | xxx | xxx | xxx |
Customer Sales by Year & Product (Combined Matrix)
Customer Name | 2018 | 2019 | 2020 | Product A | Product B | Product C |
aaaaaaaaaaaaaaaaaaaaaaaaaa1 | xxx | xxx | xxx | xxx | xxx | xxx |
aaaaaaaaaaaaaaaaaaaaaaaaaa2 | xxx | xxx | xxx | xxx | xxx | xxx |
aaaaaaaaaaaaaaaaaaaaaaaaaa3 | xxx | xxx | xxx | xxx | xxx | xxx |
Solved! Go to Solution.
Hi @Anonymous
Sorry, I don't think it's accessible under live-connection.
Hi @Anonymous
1. If the live-connection is caused by connecting the dataset from power bi service, you can just download the pbix and manage the relationship in desktop like below:
2. You can also use the LOOKUPVALUE function by DAX to create a measure as value:
https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax
Hi Dina,
Thanks for your response! Unfortunately, I'm working with a live connection to a SQL analysis service (the underlying model is tabular). So I can't change anything in the data model; relationships, columns, tables, etc. To clarify, both product and year are already connected to customer name. Simplified, the data looks something like this:
Customer Name | Year | Product | Sales |
C1 | 2019 | P1 | 10 |
C1 | 2019 | P2 | 20 |
C1 | 2020 | P1 | 31 |
C1 | 2020 | P2 | 42 |
And I want to have a visual that displays the data like this:
Customer Name | 2019 | 2020 | P1 | P2 |
C1 | 30 | 73 | 41 | 62 |
So the problem is how I could show Sales (value) by Customer names (rows), using two different dimensions as column headers at the same time (Year & Product). Backend, everything is already connected.
Hi @Anonymous
Sorry, I don't think it's accessible under live-connection.
I have gotten that impression as well. Thanks anyhow!
See if The New Hotness helps, it was specifically designed to support custom matrix columns. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/td-p...
Thanks Greg! 🙂 If I compromise on a few things this solution may work. But I would prefer not to hardcode the product columns since there are a lot of them, they tend to change, and I want them to change dynamically depending on the customer selection (the same goes for year). Furthermore, I would like to preserve the product hierarchy used for drilldown. I'll try your solution out; if it turns out alright, I'll make sure to mark the post as resolved.
PS: I hadn't seen this trick before. If it doesn't work for this problem, it sure does reduce the headache associated with some other tables. Thanks!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
72 | |
62 | |
52 | |
48 |
User | Count |
---|---|
208 | |
89 | |
61 | |
59 | |
57 |