Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
deeksha
Helper II
Helper II

Actual Vs Budget report

Hi all,

I Have two tables, the first one contains the budget transactions,their date and main accounts with dimensions and the second one contains the actual transactions, transaction dates and their main accounts with dimensions.

 

The problem is when i am using a slicer to display the transactions according to their dates, when i am using the dates of budget table it is showing only the correct budget transactions and when i am using actual date in slicer it is filtering out only the actual transactions.

 

I also tried to create a calendar date table and made a relationship with the other two tables but it does not work.

I want a date slicer which filters out both the actual transactions and budget transactions accordingly, which date should i use?

Any help is really appreciated.

2 ACCEPTED SOLUTIONS

Checked your PBIX file.

 

You are not getting the results because there is a circular/ambiguous relationship. As a result, one of the relationships from the Calendar table is inactive. Refer to the image below.

Image1.png

 

First, you should delete the relationship between the field "Display Value"  in both the table. 

Then edit the inactive (dotted line) relationship from the Calendar table and make it active.

 

It will resolve the issue.

 

Then create a separate table for "Display Value" and connect the same to the Display Value field for both the tables. This will avoid a circular relationship. Refer to the image below

 

Image2.png

 

You should then use the "Display Value" field from the new table in the slicer.

 

Conceptually that's about it. 

 

View solution in original post

Looks okay. But my suggestion is that you create a calculated table for "Display Values" using a formula like this...

 

DisplayValues = DISTINCT(
                    UNION(ALL('BudgetTransactions (2)'[DisplayValue]),
                            ALL(DimensionAttributeValueCombinations[DisplayValue])
                    )
                )

 

This will pick up the unique display values from both the tables and union them to give you all the distinct display values from both the table. It is up to you based on what you want.

 

Once you have a calculated table like I mentioned above with all the distinct DisplayValues from both the table, you will be able to calculate the Variances between Budget and Actual easily because the relationships will become 1 to Many.

View solution in original post

17 REPLIES 17

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.