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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Employee
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
Employee
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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors