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

How to find variance between columns from separate tables

Here is my data: https://ibb.co/1bH6QSF

 

I am trying to find the variance (difference) between the first and middle table in terms of expected sales and sale total (these are the actual sales), at the product category level (Table 3), for each year, quarter, and month.

 

How would I go about doing this? I suppose there are two preliminary steps:

 

1) Create a date-variable that links to the 3 tables.

 

2) Create a model that links the three tables together

 

The given date variables are weird, however, e.g. in table 1, I have "Year" and "month" in two separate columns. How do I consolidate this?

 

Ultimately, I want to create something similar to this: https://ibb.co/r4rgDfS

 

That can show me the "actual sales"  (total sale in my data) vs "target sales" (forecasted sales in my data), at a Year, quarter, or month level.

Any help is appreciated, thank you!

 

1 ACCEPTED SOLUTION
Super User I
Super User I

Hi @Analytics4Life1 ,

new date = CONCATENATE("1/",MAX('Table (2)'[Month]))&"/"&max('Table (2)'[Year ])//You can use either CONCATENATE() (which only takes two values, or & to combine Text. Nee to use quotes around any text.

 

nd.PNG


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel


If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you. 

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Regular Visitor

Hi Nathaniel, this is perfect, and I think I can take it from here 🙂

Hi @Analytics4Life1 ,

Actually I wrote that for a measure, not a column. So if you use it as a column, you don't need the MAX() functions. Sorry didn't see that until I looked again at the picture.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Super User I
Super User I

Hi @Analytics4Life1 ,

new date = CONCATENATE("1/",MAX('Table (2)'[Month]))&"/"&max('Table (2)'[Year ])//You can use either CONCATENATE() (which only takes two values, or & to combine Text. Nee to use quotes around any text.

 

nd.PNG


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel


If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you. 

Please read this post to get your question answered more quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors