Forum Discussion
Related Group sales
Hello,
I am trying to create a power bi app to analyse some product groups and I need some help.
I have a table containing sales by product and other attributes. For a period, my products are gather together to be sold at one price. So for a period, I have several groups of products that are sold at different price. For a period, a product can belong at most at 1 group.
Groups live only for a period of time (1 week or 2 weeks,...) Products can be in different groups over time. So for a week a product1 and others can be in a group A then 2 weeks later the product1 can be in another group with the same products and/or different products.
My goal in power bi is to be able to select a group of product in a slicer , then to display in a visulaization where I can view the average sales of the groups where there is at least one product in my selected group.
Exemple: If I have a
group1 with product A,B, C sold at a unit price P1
group2 with product A, B ,D sold at P2
group3 with product E, F, G sold at P2
group4 with product A, B, J sold at P2
If in a slicer, if I select group1 I would like to see in a viz, the unit price and the average sales for each one of this unit price so
P1 = avg(Group1 Sales)
P2 = avg(Group2 Sales, Group4 Sales)
If I select group2 or 4, I would see the same
If I select group 3, I will only see P3 = Avg(group3 Sales)
I think that my problem is probably more dax related to be able to get the group that contains my products.
I hope that my explanation is clear, Does any one have an idea how I can acheive to do that. For now I only have 1 table with all the informations of products, sales, unit price and groups
Thanks
Hi Anonymous,
According to your description above, I made a sample for your reference.
I assume you have a table called "Table1" like below.
1. Create a new table called "Table2" with duplicate values of "products" column and "groups" column from "Table1" like below.(Note: Make sure there no any relationship between "Table1" and "Table2")
2. Use the formula below to create a measure called "CountOfSelectedProduct" within "Table1".
CountOfSelectedProduct = COUNTROWS ( FILTER ( Table1, CONTAINS ( Table2, Table2[products], Table1[products] ) ) )3. Show Table2[groups] as a Slicer on the report.
4. Show Table1[unit price], and Table1[sales](with aggregation of Average) on the Table visual.
5. Apply Table1[products] as visual level filter with TopN CountOfSelectedProduct measure like below.
6. Select a group from the Slicer, then you should get your expected result.
Here is the sample pbix file for your reference.:smileyhappy:
Regards
1 Reply
Hi Anonymous,
According to your description above, I made a sample for your reference.
I assume you have a table called "Table1" like below.
1. Create a new table called "Table2" with duplicate values of "products" column and "groups" column from "Table1" like below.(Note: Make sure there no any relationship between "Table1" and "Table2")
2. Use the formula below to create a measure called "CountOfSelectedProduct" within "Table1".
CountOfSelectedProduct = COUNTROWS ( FILTER ( Table1, CONTAINS ( Table2, Table2[products], Table1[products] ) ) )3. Show Table2[groups] as a Slicer on the report.
4. Show Table1[unit price], and Table1[sales](with aggregation of Average) on the Table visual.
5. Apply Table1[products] as visual level filter with TopN CountOfSelectedProduct measure like below.
6. Select a group from the Slicer, then you should get your expected result.
Here is the sample pbix file for your reference.:smileyhappy:
Regards