Forum Discussion

lusalva's avatar
lusalva
Frequent Visitor
9 years ago

Card result based on two different filters

Hi,

I have a report where I show some KPIs ( Sales_Values,  Average_sales, Sales_qty, etc...) and I have tow filters: One which I show the information for product 1 and the Second showing the information for product 2.
In the filter 1 I configured to not have an interaction with filter 2 and filter 2 I configured to not interact with filter 1, so everytime I select a product in filter 1 , the filter 2 shows the whole list of products and the other way around as well.

So when I select product 1 in the filter 1, I show the Sales value for product 1 in a card and when I select product 2 in filter2 I show the sales value for product 2 in another card.

But when I create a new column which is the difference between the sales of product 1 vs product 2 and try to show it on a third card, it shows as blank.
What´s the best way to show a value in a card that has an influence of two different filters?

Basically the formula I am using to show the difference is : prod_1_vs_prod_2 = (([sales_prod_2])/([sales_prod_1]))-1

 

Show and Tell

8 Replies

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

    Hi lusalva,

     

    In order to avoid the influence between two slicers, rather than adding the same column in two slicers, rather than referring to the same data table in two measures, you should recreate a second table which contains all the columns in original table.

     

    In slicer1, add product column from table1, in slicer2, add product column from table2. In measure [sales_prod_1], calculate the values in table1 and calculate values in table2 in measure [sales_prod_2].

     

    Best regards,
    Yuliana Gu

    • lusalva's avatar
      lusalva
      Frequent Visitor
      9 years ago

      Hi Yuliana,

       

      I tried that now, and it does not work.

       

      I duplicated the table and called benchmark_2, so now on slicer 1 I entered the sales_prod_1 that contains values for products on benchmark_1 and on slicer 2 I entered the sales_prod_2 that values for products on benchmark_2 and in the card I have created a new colunm called 'variation_%' that is calculated this way : variation_% = ((BENCHMARK_1[sales_prod_1 ])/(RELATED(BENCHMARK_2[sales_prod_2 ])))-1

      Also I have set the card to not have any interaction 

       

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

        Hi lusalva,

         

        In my test, I could get desired result if I duplicated the table.

         

        In table1, I created a measure: 

        Total1 = CALCULATE(SUM(ClosedTickets[Amount]),ALLEXCEPT(ClosedTickets,ClosedTickets[Category]))

        In table2, i created Total2:

        Total2 = CALCULATE(SUM('Closed Tickets2'[Amount2]),ALLEXCEPT('Closed Tickets2','Closed Tickets2'[Category2]))

         

        Measure variation_% = DIVIDE([Total1],[Total2])-1

         

        In visual surface:

         

        If in your scenario, you always get 0% in the card which displays variation_%, please format its decimal place.

         

        Regards,
        Yuliana Gu