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

Table Relationships for Actual vs Budget analysis

I am preparing a dashboard for Budget vs. Actual sales analysis. I have different lookup tables, one data table and three budget tables. I am facing difficulty in creating right type of relationships that will join all these tables to help me in creating KPI and other visuals. I cant combine them in one data table rather I have to keep all these table separately.  Please help https://www.dropbox.com/sh/3bbrcpezh5d8h9u/AACDR0gNFc6pAtcvcpOctYaTa?dl=0

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Table Relationships for Actual vs Budget analysis

Hi @muhammadimran 

 

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution. if not, please share more details about your question, we'd like to provide further support. thanks!

 

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

View solution in original post

6 REPLIES 6
mussaenda Senior Member
Senior Member

Re: Table Relationships for Actual vs Budget analysis

Hi @muhammadimran ,

 

Thank you for providing a dummy data.

2019_08_27_15_52_22_Untitled_Power_BI_Desktop.png

 

Here you can see how they are related.

 

2019_08_27_15_36_38_Actual_vs_Target_sample_tables_Protected_View_Excel.png

 

I added one more table (product lookup table)

Is this okay with you?

 

Others will have more efficient answer. 

Highlighted
muhammadimran Regular Visitor
Regular Visitor

Re: Table Relationships for Actual vs Budget analysis

Thanks for your support and taking the time to solve this query. I am trying to replicate it but please tell me what new columns you have created in Target, Calendar and Revenue tables.

mussaenda Senior Member
Senior Member

Re: Table Relationships for Actual vs Budget analysis

Hi @muhammadimran ,

 

link of pbix is here

 

You can see the new columns created

muhammadimran Regular Visitor
Regular Visitor

Re: Table Relationships for Actual vs Budget analysis

I applied the solution but now facing another error. I have some time intelligence functions in my dashboard which I am using to create bar charts showing current and same period last year results. here is the error message.

 

"Function SAMEPERIODLASTYEAR expects a contiguous selection when the date column comes from a table on the 1-side of a bi-directional relationship."

 

Please help me to resolve. I dont want to change the cross filter diection.

https://www.dropbox.com/s/2iotg0ckh1nb2c9/Error.PNG?dl=0

mussaenda Senior Member
Senior Member

Re: Table Relationships for Actual vs Budget analysis

Community Support Team
Community Support Team

Re: Table Relationships for Actual vs Budget analysis

Hi @muhammadimran 

 

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution. if not, please share more details about your question, we'd like to provide further support. thanks!

 

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

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,072)