Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
so i have 4 columns of binary data
i want to be able to filter through the combinations of the 4 how can i do that?
ie if i have product 1 and product 2 vs having all 4 products?
when i try to them into a treemap with their counts in values i just get a total count and selecting each combination does nothing
Hi @Anonymous
Any chance you can post a small example set of data? That might make it easier to understand what you are after. 🙂
1 meaning they have that item and 0 meaning the dont
i want to be able to see the different combinations of items customers buy and how it affects my total revenue
Hi @Anonymous
So for the above example, what is your ideal result? Can you also mock that up in Excel?
i guess something like this where i am able to see the different impacts of item combination on this particular KPI
If you only have 4 columns as per your example you could just create a new calculated column like this
mix = 'Table1'[Item 1] & "," & 'Table1'[Item 2] & "," & 'Table1'[Item 3] & "," & 'Table1'[Item 4]
and then drag that to a Grid and sum your Revenue column. I suspect your real example isn't so straight forward??
i have 4 items i need combinations of it sounds like this would be listing out all 24 combinations for separate revenue
i was hoping to be able to use a treemap so you could select the combinations better and accounting for these different combinations would happen behind the scenes automatically like when im selecting different regions for sales data across the states
Hi @Anonymous,
i was hoping to be able to use a treemap so you could select the combinations better and accounting for these different combinations would happen behind the scenes automatically like when im selecting different regions for sales data across the states
Is your expected result similar like below?
If so, you can firstly use the formula provided by @Phil_Seamark above to add a calculate column to your table.
mix = 'Table1'[Item1] & "," & 'Table1'[Item2] & "," & 'Table1'[Item3] & "," & 'Table1'[Item4]
Then, you can use the new create calculate column as Group on the Treemap, and use the four Item column as Slicers to get your expected result on the report.
Here is the sample pbix file for your reference.
Regards
thats the thing if you write out all the combinations like that it doesnt scale
this treemap for combinations should be very simple
perhaps its my set up
Hey @Anonymous
maybe you are looking for something like this, taken from this little example
The table showh a normal sales table, I created a calculated column "Item_Mix" that concatenates the items purchased by each customer, this is the dax statement:
Item_Mix = CALCULATE( CONCATENATEX(VALUES(Sales[Item]),'Sales'[Item],","), ALLEXCEPT('Sales',Sales[Customer_ID]) )
Using a calculated column enables you to use this column in visuals or as a slicer.
The problem here is the underlying assumption, that the concatenation can be performed as a calculated column. Meaning that the calculation can become much more complex if it has to consider more columns.
A calculated column is necessary due to the effect, that measures currently can not be used on axis or as content for a slicer - this is for a couple of reasons and i would assume that this will not change in the near future.
Hope this helps
Tom
Hi @Anonymous
I'm keen to understand more about what you mean about selecting combinations.
Feel free to DM me with a PBIX file with more detail so I can help more.
and are those rows individual transactions or purchases over time?
their purchases over time, not transactions
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |