Forum Discussion
How to make dynamic matrix table "values"
So i basically have an excel tab of a store's monthly volume breakdown and then the top 5 customers monthly breakdown. However, i cannot seem to make the volume value change when i dropdown - i've attached a crudely photoshopped table of what the idea table would look like. Any pointers?
IDEAL First drop down:
The data:
| Store | Period | Vol (L) |
| no.1 | Jan-24 | 211 |
| no.1 | Feb-24 | 250 |
| no.1 | Mar-24 | 315 |
| no.1 | Apr-24 | 354 |
| no.1 | Jun-24 | 679 |
| no.1 | Jul-24 | 927 |
| no.1 | Aug-24 | 645 |
| no.1 | Sep-24 | 392 |
| no.1 | Oct-24 | 677 |
| no.1 | Nov-24 | 403 |
| no.1 | Dec-24 | 78 |
| no.1 | Jan-25 | 101 |
| no.1 | Feb-25 | 249 |
| Store | Customer | Period | Vol (L) |
| no.1 | A | Jan-24 | 211.32 |
| no.1 | A | Apr-24 | 190.9 |
| no.1 | A | Feb-24 | 189.19 |
| no.1 | A | Jul-24 | 185.6 |
| no.1 | A | Sep-24 | 166.75 |
| no.1 | A | Mar-24 | 154.67 |
| no.1 | B | Jul-24 | 491.51 |
| no.1 | B | Jun-24 | 97.8 |
| no.1 | AL | Oct-24 | 115.47 |
| no.1 | AL | Aug-24 | 98.61 |
| no.1 | AL | Jun-24 | 81.83 |
| no.1 | AL | Feb-25 | 77.8 |
| no.1 | AL | Feb-24 | 61.07 |
| no.1 | M | Jun-24 | 136.8 |
| no.1 | M | Apr-24 | 117.06 |
| no.1 | M | Sep-24 | 116.37 |
| no.1 | C | Oct-24 | 263.75 |
| no.1 | C | Jan-25 | 69.14 |
When a chart shows all the time the same value (like it is the case for you)
it means there is no relationship between your two tablesSo first, you need to link your table:
After that, the function hasonevalue will help you to reach what you need
_Test = IF(HASONEVALUE(Customer[Customer]),SUM(Customer[Vol (L)]),sum(Store[Vol (L)]))This is the result I get with this function and your dataset (I add some values for No 2)If it is not what you try to achieve, can you give us more data and more context for your case?
2 Replies
When a chart shows all the time the same value (like it is the case for you)
it means there is no relationship between your two tablesSo first, you need to link your table:
After that, the function hasonevalue will help you to reach what you need
_Test = IF(HASONEVALUE(Customer[Customer]),SUM(Customer[Vol (L)]),sum(Store[Vol (L)]))This is the result I get with this function and your dataset (I add some values for No 2)If it is not what you try to achieve, can you give us more data and more context for your case?
HI derekli1700
It seems your have relationship issue.
Can you please give proper table name and column name to both the tables .it will be easy for us to understand what columns you are dragging from which table.
because your column name is exact same in both the table.
please provide the expected output with proper column name and which column should be present in the matrix table.
