cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
olik Regular Visitor
Regular Visitor

Comparing between different filters on the same data

Say I have data like this:

 

DateAccountStatisticValue
30/06/20171Alpha0.85
30/06/20172Alpha0.34
30/06/20171Beta0.52
30/06/20172Beta0.29
30/06/20171Gamma0.14
30/06/20172Gamma0.25
31/07/20171Alpha0.35
31/07/20172Alpha0.23
31/07/20171Beta0.51
31/07/20172Beta0.26
31/07/20171Gamma0.16
31/07/20172Gamma0.90
31/08/20171Alpha0.88
31/08/20172Alpha0.20
31/08/20171Beta0.76
31/08/20172Beta0.79
31/08/20171Gamma0.83
31/08/20172Gamma0.66

 

Now imagine I have two bar charts (one per Account), with Date on X and Value on Y, and the different Statistics as different bars.

[In reality there are a lot more Accounts, and there are two slicers to select the two Accounts we want to compare.]

 

How can I get a third chart that shows the Value difference between the two, again per Date and Statistic?

 

I started by creating a duplicate of the dataset and now want to do something like https://community.powerbi.com/t5/Desktop/How-to-calculate-difference-after-different-filter-settings... - but there is no sensible way to relate the table with its duplicate. 

 

There must a way to do this surely?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Thejeswar Senior Member
Senior Member

Re: Comparing between different filters on the same data

Hi @olik,

You can try the below method as well!!! FYI, I have the data that you have provided for this purpose

 

The Following are the steps.

 

1. Have 2 copies of your data (namely Table1 and Table2)

2. Join the tables based on Date Column

3. Create the following 2 measures

 

In Table1

Measure 1 = CALCULATE(SUM(Table1[Value]), FILTER(Table1, Table1[Account] = SELECTEDVALUE(Table1[Account]))) 

In Table2

 

Measure 2 = CALCULATE(SUM(Table2[Value]), FILTER(Table2, Table2[Account] = SELECTEDVALUE(Table2[Account]))) 

Create another measure to find the difference between the measures

 

Measure = [Measure 1] - [Measure 2] 

Relationship, more likely to look like (not necessarily have to be same)

 

relationship.PNGRelationship between tables through a bridge table

My Output looked as shown below

 

difference.PNGOutput

 

 As you change the accounts in your slicer, you value changes correspondingly....

 Hope this helps you get what you needed!!!

 

regards,

Thejeswar

 

olik Regular Visitor
Regular Visitor

Re: Comparing between different filters on the same data

Thanks a lot! I used your suggestion and made the formula even easier:

 

 

Result = SUM(Table1[Value]) - CALCULATE(SUM(Table1[Value]), FILTER(ALL(Table1[Account]),Table1[Account] = SELECTEDVALUE(Table2[Account])))

 

That way I only need one measure, and technically I don't even need the full duplicate table... Just another list of accounts would do!

 

Do you see any issues with my approach? Still very new to the whole DAX coding!

 

5 REPLIES 5
olik Regular Visitor
Regular Visitor

Re: Comparing between different filters on the same data

I think I have found a way, using an additional measure based on SELECTEDVALUE parameters and then defined as such:

 

CALCULATE
(
AVERAGE(Table1[Value]),
FILTER(
ALL(Table1[Account]),
[Account]=Table2[Selected Account]
)
)

 

Surely that can't be the best way though?

Thejeswar Senior Member
Senior Member

Re: Comparing between different filters on the same data

Hi @olik,

You can try the below method as well!!! FYI, I have the data that you have provided for this purpose

 

The Following are the steps.

 

1. Have 2 copies of your data (namely Table1 and Table2)

2. Join the tables based on Date Column

3. Create the following 2 measures

 

In Table1

Measure 1 = CALCULATE(SUM(Table1[Value]), FILTER(Table1, Table1[Account] = SELECTEDVALUE(Table1[Account]))) 

In Table2

 

Measure 2 = CALCULATE(SUM(Table2[Value]), FILTER(Table2, Table2[Account] = SELECTEDVALUE(Table2[Account]))) 

Create another measure to find the difference between the measures

 

Measure = [Measure 1] - [Measure 2] 

Relationship, more likely to look like (not necessarily have to be same)

 

relationship.PNGRelationship between tables through a bridge table

My Output looked as shown below

 

difference.PNGOutput

 

 As you change the accounts in your slicer, you value changes correspondingly....

 Hope this helps you get what you needed!!!

 

regards,

Thejeswar

 

olik Regular Visitor
Regular Visitor

Re: Comparing between different filters on the same data

Thanks a lot! I used your suggestion and made the formula even easier:

 

 

Result = SUM(Table1[Value]) - CALCULATE(SUM(Table1[Value]), FILTER(ALL(Table1[Account]),Table1[Account] = SELECTEDVALUE(Table2[Account])))

 

That way I only need one measure, and technically I don't even need the full duplicate table... Just another list of accounts would do!

 

Do you see any issues with my approach? Still very new to the whole DAX coding!

 

Thejeswar Senior Member
Senior Member

Re: Comparing between different filters on the same data

Hi @olik,

Your re-did DAX looks fine.

 

I think the new DAX that you have written should be good enough to get the difference

 

 

Regards,

Thejeswar

v-jiascu-msft Super Contributor
Super Contributor

Re: Comparing between different filters on the same data

Hi @olik,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

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

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 288 members 3,111 guests
Please welcome our newest community members: