- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Calculate the difference between two columns (with...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

dancarr22

Regular Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-22-2018
09:43 AM

Hello,

We have tickers for which we receive prices from multiple vendors on a daily basis.

i.e. We receive prices from 4 vendors for ticker ABC.

We would like to show the difference (actual and as a %) between the different data sources in a matrix.

Should display the difference between current record and record to the left. So, for Ticker ABC - would show difference between Bloomberg and IDC in column IDC.

Bascially want to make this dynamic so if user chooses only 2 pricing sources it will calculate the difference between those two. But, if three are chosen then if will show the difference between 1 and 2 -- then 2 and 3.

Basically want this to work like Tableau (not trying to promote Tableau -- just wanted to show example of how it should work)

Assuming some way to do this in DAX? Changed quick measures and couldn't find anything relevant.

Thanks,

Dan

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

v-shex-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-03-2018
01:46 AM

Hi @dancarr22,

Index column is a custom column I created in query edit.

Since power bi data model not contains column index and row index, you need one index to let formula calculation dynamic based on current index.(such current -1 or current +1)

Date or numeric column can be used as index, text character current not support compare with math symbols.

If you only need to calculate on specific source, you can try to use switch function to define them as specific numeric as calculation index.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng

If this post**helps**, then please consider *Accept it as the solution* to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |

If this post

For learning resources/Release notes, please visit: | |

4 REPLIES 4

v-shex-msft

Community Support Team

Re: Calculate the difference between two columns (without hard coding in formula) in matrix

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-23-2018
01:59 AM

HI @dancarr22,

I think you need to add index column based on 'pricing source' column to let formula dynamic.

Sample formula:

Diff = VAR currIndex = MAX ( Table3[Index] ) VAR currPrice = CALCULATE ( SUM ( Table3[Price] ), FILTER ( ALLSELECTED ( Table3 ), Table3[Index] = currIndex ), VALUES ( Table3[Ticker] ), VALUES ( Table3[Date] ) ) VAR prevPrice = CALCULATE ( SUM ( Table3[Price] ), FILTER ( ALLSELECTED ( Table3 ), Table3[Index] = currIndex - 1 ), VALUES ( Table3[Ticker] ), VALUES ( Table3[Date] ) ) RETURN IF ( prevPrice <> BLANK (), currPrice - prevPrice, currPrice )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng

If this post**helps**, then please consider *Accept it as the solution* to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |

If this post

For learning resources/Release notes, please visit: | |

dancarr22

Regular Visitor

Re: Calculate the difference between two columns (without hard coding in formula) in matrix

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-28-2018
09:14 AM

Hi Xiaoxin,

Thanks for your assistance with this.

Unfortunately, the solution you provided did not work.

Issue appears to be in the index logic. while we do have indexes associated with the price source they are not in strict numerical order. Instead of using "currIndex - 1" is there some other way to define the selected indexes?

If not, can you just provide an example where we do price source 'Bloomberg' vs price source 'Reuters'? Not the optimal dynamic solution but can go with this for now.

Thanks,

Dan

v-shex-msft

Community Support Team

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-03-2018
01:46 AM

Hi @dancarr22,

Index column is a custom column I created in query edit.

Since power bi data model not contains column index and row index, you need one index to let formula calculation dynamic based on current index.(such current -1 or current +1)

Date or numeric column can be used as index, text character current not support compare with math symbols.

If you only need to calculate on specific source, you can try to use switch function to define them as specific numeric as calculation index.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng

If this post**helps**, then please consider *Accept it as the solution* to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |

If this post

For learning resources/Release notes, please visit: | |

dancarr22

Regular Visitor

Re: Calculate the difference between two columns (without hard coding in formula) in matrix

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-11-2018
02:32 PM

Thanks Xiaoxin