Card result based on two different filters

[lusalva]

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

[v-yulgu-msft]

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]

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]

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]

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

[v-yulgu-msft]

Hi @lusalva,

Yes, you are right.

Regards,
Yuliana Gu

[lusalva]

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]

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

[lusalva]

Thank you