Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
v-ljerr-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

 

According to your description above, I made a sample for your reference.

 

I assume you have a table called "Table1" like below.

 

t1.PNG

 

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")

 

t2.PNGr1.PNG

 

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.

 

slicer1.PNG

 

4. Show Table1[unit price], and Table1[sales](with aggregation of Average) on the Table visual.

 

t3.PNG

 

5. Apply Table1[products] as visual level filter with TopN CountOfSelectedProduct measure like below.

 

f1.PNG

 

6. Select a group from the Slicer, then you should get your expected result.

 

result1.PNGresult2.PNG

 

Here is the sample pbix file for your reference.Smiley Happy

 

 

Regards

View solution in original post

1 REPLY 1
v-ljerr-msft
Microsoft Employee
Microsoft Employee

Hi @Anonymous,

 

According to your description above, I made a sample for your reference.

 

I assume you have a table called "Table1" like below.

 

t1.PNG

 

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")

 

t2.PNGr1.PNG

 

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.

 

slicer1.PNG

 

4. Show Table1[unit price], and Table1[sales](with aggregation of Average) on the Table visual.

 

t3.PNG

 

5. Apply Table1[products] as visual level filter with TopN CountOfSelectedProduct measure like below.

 

f1.PNG

 

6. Select a group from the Slicer, then you should get your expected result.

 

result1.PNGresult2.PNG

 

Here is the sample pbix file for your reference.Smiley Happy

 

 

Regards

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.