Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate ratios based on filter selection

Hi all,

 

I have a table in PowerBI with the following columns: 

 

Full table

 

Article numberShop namePriceDate
123abc2022/09/20
123def2122/09/20
123ghi2122/09/20
456abc1622/09/20
456def1722/09/20
456ghi1822/09/20

 

Now my goal is to (on a given date; let's say 22/09/20) calculate the price ratio (price of one shop divided by the average of other shops) on individual article number level. This ratio should be based on two slicers that I have added to my report:

- One where I select the Shop name that I would like to pick ("selected shop")

- One where I select the Shop name OR Shop names that I would like to compare against ("compared shops")

The price ratio would then be: Price selected shop / Average ( price compared shops) 

 

If Selected Shop = abc (first slicer) && Compared Shops = def & ghi, I would like to get the following table: 

 

Price ratio table

Article numberPrice  selected shopAverage price compared shopsRatio
123202120/21
4561617.516/17.5


Can someone explain to me what a good approach for this problem would be? I have already managed to create a measure that I can use to create the "Price selected shop" column in the above table (using a filter based on a second table that I added to the powerBI file). However, I haven't been able to calculate an average price per article number, based on a slicer-selection of rows that I would like to take into account. 

 

Hopefully someone can help me further!

 

Many thanks in advance!

 

Need Help
Tips and Tricks
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    Anonymous 

     

    See if this is what you need (this method allows for mutliple selections in both slicers)

     

    If so, the model is set up like this. (Notice there is a duplicate of the Shop Name Dimension Table, called 'Shop Name (comparison)', which is what is used to select the shop(s) as comparison to the shops selected in the main slicer)

     

    The first thing I did was create a filter measure to exclude the shops selected in the main shop slicer from the 'Shop Name (comparison)' slicer dynamically:

     

    Filter comparison = 
VAR ShopN = VALUES('Shop name'[Shop Name])
VAR Comp =VALUES('Shop name (comparison)'[Shop Name])
Return
COUNTROWS(EXCEPT(Comp, ShopN))

     

    I then added this measure and (established a vlue of 1) to the "Filters for this visual" in the filter pane for 'Shop Name (comparison)':

     

    Next write the appropriate measures:

    1) to get the average price of the shops selected in the main slicer:

     

    Price = AVERAGE(Data[Price])

     

    2) To calculate the average price of the other shops selected for comparison purposes:

     

    Average Price Comp (shops) = 
VAR calc =CALCULATE([Price], TREATAS(VALUES('Shop name (comparison)'[Shop Name]), 'Shop name'[Shop name]))
RETURN
IF(AND(ISFILTERED('Shop name'[Shop Name]), ISFILTERED('Shop name (comparison)'[Shop Name])), calc)

     

    If no selection is made in either slicer, this measure returns Blank()

     

    3) The ratio measure:

     

    Ratio = IF(AND(ISFILTERED('Shop name'[Shop Name]), ISFILTERED('Shop name (comparison)'[Shop Name])), 
[Price] & "/" & [Average Price Comp (shops)])

     

     

    I've attached the PBIX file for you reference

  • Anonymous's avatar
    Anonymous
    5 years ago

    Great - I'll add the count to the table to show the totals. Thanks for this.

     

    A last question (I promise 😉 ) I have:

    I am trying to plot the ratios in a bar chart (histogram: price ratios on the x-axis, count of #articles per bucket on the y-axis). I know that measures cannot be used as an axis in a histogram. What would be a good way to approach this? I have created a new table "X-Axis" with all the buckets that I'd like to show in the graph. Using a measure "Count" I try to count whether the measure Ratio is within a bucket or not. I use the following formula:

     

    Count = COUNTROWS(FILTER('EANs',[Ratio]>=MIN('X-Axis'[Value])&&[Ratio]<=MAX('X-Axis'[Value2])))

     

    The number of "counts" that are shown in the bar graph, however, seems to be way smaller than the number of articles shown in the table we just created. 

     

    What could potentially be the reason for this?

     

    Thanks!

     

5 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    Anonymous 

     

    See if this is what you need (this method allows for mutliple selections in both slicers)

     

    If so, the model is set up like this. (Notice there is a duplicate of the Shop Name Dimension Table, called 'Shop Name (comparison)', which is what is used to select the shop(s) as comparison to the shops selected in the main slicer)

     

    The first thing I did was create a filter measure to exclude the shops selected in the main shop slicer from the 'Shop Name (comparison)' slicer dynamically:

     

    Filter comparison = 
VAR ShopN = VALUES('Shop name'[Shop Name])
VAR Comp =VALUES('Shop name (comparison)'[Shop Name])
Return
COUNTROWS(EXCEPT(Comp, ShopN))

     

    I then added this measure and (established a vlue of 1) to the "Filters for this visual" in the filter pane for 'Shop Name (comparison)':

     

    Next write the appropriate measures:

    1) to get the average price of the shops selected in the main slicer:

     

    Price = AVERAGE(Data[Price])

     

    2) To calculate the average price of the other shops selected for comparison purposes:

     

    Average Price Comp (shops) = 
VAR calc =CALCULATE([Price], TREATAS(VALUES('Shop name (comparison)'[Shop Name]), 'Shop name'[Shop name]))
RETURN
IF(AND(ISFILTERED('Shop name'[Shop Name]), ISFILTERED('Shop name (comparison)'[Shop Name])), calc)

     

    If no selection is made in either slicer, this measure returns Blank()

     

    3) The ratio measure:

     

    Ratio = IF(AND(ISFILTERED('Shop name'[Shop Name]), ISFILTERED('Shop name (comparison)'[Shop Name])), 
[Price] & "/" & [Average Price Comp (shops)])

     

     

    I've attached the PBIX file for you reference

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Wow, thanks a lot - it's working. I already thought about adding two tables for the different slicers, but the 2 measures in a different measure table indeed did the trick. Thanks for that! 

       

      As a follow-up I added a filter to exclude articles with no price for the selected shop OR with no average price for the selected compared shops. The table now shows all values where a valid comparison can be made! 

       

      A next question would be: what table do I need to COUNTROWs off in order to get the total number of products that is listed in the table: "average price comparison". 

       

      The measure that I created (Count overlap = COUNTROWS('Data')) is showing a lower number of articles than in fact are shown in the table that we just created. 

       

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        5 years ago

        Anonymous 

        Glad it helped!

         

        As regards your question:

        "A next question would be: what table do I need to COUNTROWs off in order to get the total number of products that is listed in the table: "average price comparison". "

         

        If you wan to count the items in the context of the table visual (which you have filtered further), try
        DISTINCTCOUNT(Article Number [Article Number])
        or
        DISTINCTCOUNT(Data[Article Number])
        (depending on which field you are using in the visual.

         

        If you want to count ALL Articles with an [Average Price comp], try:
        Number of articles comp = CALCULATE(DISTINCTCOUNT(Data[Article Number], FILTER(ALL(Data), NOT(ISBLANK([Average Price comp]))))