Related Group sales

[Anonymous]

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

[v-ljerr-msft · Accepted 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