Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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 ar...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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