cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
senescence Frequent Visitor
Frequent Visitor

How to calculate difference between two tiles with different slicers dynamically?

Hi,

 

I suspect there is no solution to this but here goes:

 

I have data in a table as follows:

 

High, 01/09/17

High, 01/09/17

Medium, 01/09/17

Low, 01/09/17

High, 02/09/17

Medium, 02/09/17

Medium, 02/09/17

Low, 02/09/17

High, 03/09/17

Medium, 03/09/17

Low, 03/09/17

Low, 03/09/17

 

In my report I have two tables and two slicers (that only work on one table each). So a user selecting 01/09/17 in Slicer A and 03/09/17 in Slicer B gets:

 

Table A:

 

High, 2

Medium, 1

Low, 1

 

Table B:

 

High, 1

Medium, 1

Low, 2

 

What I want is to show the difference between these two tiles:

 

Difference:

 

High, -1

Medium, 0

Low, 1

 

It is similar to this problem but the solution here doesn't allow the user make changes using the slicers as the filter is apllied in the query editor:

 

https://community.powerbi.com/t5/Desktop/How-to-calculate-difference-after-different-filter-settings...

 

I'm new to Power BI so any help appreciated.

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
v-yulgu-msft Super Contributor
Super Contributor

Re: How to calculate difference between two tiles with different slicers dynamically?

Hi @senescence,

 

To achieve this requirement, the date column added into two slicers have to come from two unrelated tables. So, in this scenario, you should create two calendar tables:

Calendar Date = CALENDAR(MIN('Test'[Date]),MAX('Test'[Date])) 

Calendar Date 2 = CALENDAR(MIN('Test'[Date]),MAX('Test'[Date]))

Then, create three measures like below:

Count1 = CALCULATE(COUNT('Test'[Date]),FILTER('Test','Test'[Date]=MAX('Calendar Date'[Date])))
Count2 = CALCULATE(COUNT('Test'[Date]),FILTER('Test','Test'[Date]=MAX('Calendar Date 2'[Date])))

diff = [Count2]-[Count1]

Add corresponding columns and measures into table visual. Notice that the date in slicer1 comes from 'Calendar Date', while date in slicer2 should come from 'Calendar Date 2'.

3.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
v-yulgu-msft Super Contributor
Super Contributor

Re: How to calculate difference between two tiles with different slicers dynamically?

Hi @senescence,

 

To achieve this requirement, the date column added into two slicers have to come from two unrelated tables. So, in this scenario, you should create two calendar tables:

Calendar Date = CALENDAR(MIN('Test'[Date]),MAX('Test'[Date])) 

Calendar Date 2 = CALENDAR(MIN('Test'[Date]),MAX('Test'[Date]))

Then, create three measures like below:

Count1 = CALCULATE(COUNT('Test'[Date]),FILTER('Test','Test'[Date]=MAX('Calendar Date'[Date])))
Count2 = CALCULATE(COUNT('Test'[Date]),FILTER('Test','Test'[Date]=MAX('Calendar Date 2'[Date])))

diff = [Count2]-[Count1]

Add corresponding columns and measures into table visual. Notice that the date in slicer1 comes from 'Calendar Date', while date in slicer2 should come from 'Calendar Date 2'.

3.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
senescence Frequent Visitor
Frequent Visitor

Re: How to calculate difference between two tiles with different slicers dynamically?

This worked perfectly and makes complete sense - thanks for your help.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 314 members 3,219 guests
Please welcome our newest community members: