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 inform...
Show and Tell
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

lusalva's avatar
lusalva
Frequent Visitor
9 years ago

Understood, in this case there is no relationship between the two tables right?

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

    Hi lusalva,

     

    Yes, you are right.

     

    Regards,
    Yuliana Gu

    • lusalva's avatar
      lusalva
      Frequent Visitor
      9 years ago

      Hmmm then I think I know what´s wrong on my side. The problem is that in my case I must have only one Category filter to interact with both tables and by doing this, I have linked the tables by creating an index in both tables, so whenever I select a category in the filter I will have the same products list in both filters Product 1  and Product 2.

      So when I try to apply your solution, it does not show the variation value, it keeps showing as 0,0% 

       

      Is there any way to apply this solution you suggested having both tables linked by a column in a way that I can calculate the variation% ? 

      The only other possibility that I think would be creating a cartesian join in my backend process so it will export a table with all possible combinations, but this would generate a huge ammout of data since in my products table I have more than 35k products

       

      Thanks a lot for you advices 

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

        Hi lusalva,

         

        Unfortunately, if we keep the interactions between two slicers, the measures based on them will be affected by both slicer selections, which will bring the unexpected result. 

         

        Personally, the best choice is to remove the relationship between two tables and remove the interactions between two slicers. The aim to replicate the original table is to make sure there is no influence with each other.

         

        Best regards,
        Yuliana Gu