Forum Discussion
Resolver IIFiltering matrix only on columns, but display all rows
Hi all,
I got a requirement where i have the data available in the following format
| Customer | Item sold | Item type | Amount |
| Customer1 | Item1 | Type 1 | 1 |
| Customer2 | Item2 | Type 2 | 1 |
| Customer3 | Item4 | Type 2 | 1 |
| Customer4 | Item2 | Type 2 | 1 |
| Customer5 | Item3 | Type 1 | 1 |
| Customer6 | Item4 | Type 2 | 1 |
| Customer7 | Item3 | Type 1 | 1 |
| Customer8 | Item1 | Type 1 | 1 |
| Customer9 | Item2 | Type 2 | 1 |
| Customer10 | Item4 | Type 2 | 1 |
| Customer1 | Item2 | Type 2 | 1 |
| Customer2 | Item3 | Type 1 | 1 |
| Customer3 | Item4 | Type 2 | 1 |
| Customer4 | Item3 | Type 1 | 1 |
| Customer5 | Item1 | Type 1 | 1 |
| Customer6 | Item2 | Type 2 | 1 |
| Customer7 | Item4 | Type 2 | 1 |
| Customer8 | Item2 | Type 2 | 1 |
| Customer9 | Item3 | Type 1 | 1 |
| Customer10 | Item4 | Type 2 | 1 |
My requriement is to visualize data in a matrix and allow to slice data on Item type (Type1/Type2), However still show all the customers where sale is not made for said item type.
Example output on slicing on Item Type = Type 1
| Customer | Item 1 | Item3 |
| Customer1 | 1 | |
| Customer2 | 1 | |
| Customer3 | ||
| Customer4 | 1 | |
| Customer5 | 1 | 1 |
| Customer6 | ||
| Customer7 | 1 | |
| Customer8 | 1 | |
| Customer9 | 1 | |
| Customer10 |
Similaryly, slicing on Item Type = Type 2
| Customer | Item 2 | Item4 |
| Customer1 | 1 | |
| Customer2 | 1 | |
| Customer3 | 1 | |
| Customer4 | 1 | |
| Customer5 | ||
| Customer6 | 1 | 1 |
| Customer7 | 1 | |
| Customer8 | 1 | |
| Customer9 | 1 | |
| Customer10 | 1 |
Please show me the light. :)
Hi NipponSahore,
I'm sorry for misunderstand your requirement before. Now you could follow the steps below to get your desired output.
1. Create a calculated table to get the all customers and create the relationship of the two tables.
Table = VALUES(Sheet22[Customer ])
2. Create the measure in your original table.
Measure = var a= COUNT(Sheet22[Amount ]) return IF(ISBLANK(a),0,1)
3. Then you could ceate the matrix with the new Customer you created in the Table as Rows and Item sold as Columns , the measure as Values.
You could see the difference with the picture below.
For reference, you could have a look at my pbix file which has been attached.
Hope this can help you!:smileywink:
Best Regards,
Cherry
6 Replies
v-piga-msftResident Rockstar
Hi NipponSahore,
Do you want to have the output below?
If it is your requirement, you only need to create the matrix with the Customer as Row, Item sold as Column and Amount as Values.
If I misunderstood your case, please let me know and show your expcted output, so that we can help further investigate on it?
Best Regards,
Cherry
NipponSahoreHi v-piga-msft,
Acutally my output should be be different.
I want it to display list of all the customers not just one's who've been sold the item under that type.
So, in the matrix
Rows --> All Customers
Columns --> only items which lie in Item type
Value --> 1 or 0 if item is sold or not respectively
- NipponSahore
I got a requirement where i have the data available in the following format
Customer Item sold Item type Amount Customer1 Item1 Type 1 1 Customer2 Item2 Type 2 1 Customer3 Item4 Type 2 1 Customer4 Item2 Type 2 1 Customer5 Item3 Type 1 1 Customer6 Item4 Type 2 1 Customer7 Item3 Type 1 1 Customer8 Item1 Type 1 1 Customer9 Item2 Type 2 1 Customer10 Item4 Type 2 1 Customer1 Item2 Type 2 1 Customer2 Item3 Type 1 1 Customer3 Item4 Type 2 1 Customer4 Item3 Type 1 1 Customer5 Item1 Type 1 1 Customer6 Item2 Type 2 1 Customer7 Item4 Type 2 1 Customer8 Item2 Type 2 1 Customer9 Item3 Type 1 1 Customer10 Item4 Type 2 1 My requriement is to visualize data in a matrix and allow to slice data on Item type (Type1/Type2), However still show all the customers where sale is not made for said item type.
Example output on slicing on Item Type = Type 1
Customer Item 1 Item3 Customer1 1 Customer2 1 Customer3 Customer4 1 Customer5 1 1 Customer6 Customer7 1 Customer8 1 Customer9 1 Customer10 Similaryly, slicing on Item Type = Type 2
Customer Item 2 Item4 Customer1 1 Customer2 1 Customer3 1 Customer4 1 Customer5 Customer6 1 1 Customer7 1 Customer8 1 Customer9 1 Customer10 1 Please show me the light.
