Forum Discussion
Beginner help with finding the difference between two tables
Hi RD6308 ,
Since you did not give a specific table, I had to create my own table for testing according to your description, please point out if there are any problems.
Please try below steps:
1.below is my test table
Budget: "Index" column are used to sort the "Month" column by this column
Transcations:
Model:
2. create a measure and add it to table visual
Budget VS Actuals = VAR cur_month = SELECTEDVALUE ( Budget[Month] ) VAR cur_budget = CALCULATE ( MAX ( Budget[Amount] ), Budget[Month] = cur_month ) VAR cur_actuals = CALCULATE ( MAX ( Transcations[Amount] ), Transcations[Date] = cur_month ) VAR diff = cur_actuals - cur_budget RETURN IF ( diff > 0, "+" & diff, "-" & diff )Please refer attached .pbix file
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Resolver IIHi RD6308 !
Are you familiar with the star schema in data modelling?
You will need to create a dim table for 'Date' and 'Category', and connect the dim tables to the fact tables 'Actuals' and 'Budget' with a one to many relationship.
Visual aid:
Once you have created your datamodel you can create the measures and visuals.
Get the values from the Fact tables like:
Actuals = SUM('FactActuals'[Value])
Budget= SUM('FactBudget'[Value])
Difference = [Actuals]-[Budget]
Use the dim tables as your lookup values.
e.g. Matrix: Row = Category from dimCategory ; Header = Year from DimDate ; Values = Explicit Measures from fact tbl
Hope it helps!
Kind regards,
OD