March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to Solution.
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.
Regards
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.
Regards
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
129 | |
90 | |
75 | |
58 | |
53 |
User | Count |
---|---|
200 | |
104 | |
101 | |
67 | |
55 |