Forum Discussion

Ireneliu's avatar
Ireneliu
Regular Visitor
7 years ago

Difference between two tables

Hello Everyone,   I have a question about the diference between two tables. A Table is for department's actual results. B Table is for department's budget. When the actual results have a value, ...
Ireneliu's avatar
Ireneliu
Regular Visitor
7 years ago

Hello Stachu ,

 

It was my first time publishing a question here, sorry for the incomplete information.

 

Columns for Table A are Department、AccountName、Date、accountingcurrencyamount

Columns for Table B are Department、AccountName、Date、budget

The relationship between two tables is "AccountName".
(Cardinality:many to many. Cross filter direction:both.)

 

*Measure for actual results = CALCULATE(SUM('A'[accountingcurrencyamount]),

FILTER('A',[AccountName]="Salary"|| [AccountName]="Retirement pension")

*Measure for budget = CALCULATE(SUM('B'[departmentbudget]),

FILTER('B',[AccountName]="Salary"|| [AccountName]="Retirement pension")

I used "FILTER" to choose the AccountName I need.

*Measure for difference = [Measure for actual results]-[Measure for budget]


I tried to change the relationship become Date yesterday, and got the following result.

I still can't get the right values when actual results have value...

 

Stachu's avatar
Stachu
Community Champion
7 years ago

do the following:

1) create a new table for calendar, e.g.

Calendar = CALENDARAUTO()

2) mark the Calendar tables as Date table
3) create 1:many  joins between Calendar table and both budget and actuals

4) use the month from calendar table in the visual

5) set the join between A & B tables to be done on Department

  • Ireneliu's avatar
    Ireneliu
    Regular Visitor
    7 years ago

    I followed up the steps you suggested but having a trouble in step 3.

    The relationship between Calendar table and both budget and actuals can't be active at the same time.

    • Stachu's avatar
      Stachu
      Community Champion
      7 years ago

      Hmm, that's probably because of the many:many relationship

       

      can you add a new table for Departments, e.g. like this:

      Departments =
DISTINCT (
    UNION ( VALUES ( TableA[Departments] ), VALUES ( TableB[Departments] ) )
)

      deactivate/remove your existing many:many relationship between Actuals and Budgets

      create new 1:many relationships from Departments to tables A and B

      do the same for the Calendar table

      • Ireneliu's avatar
        Ireneliu
        Regular Visitor
        7 years ago

        Sorry for late reply.

        I tried to change the relationships.

        However, when I choose Retirement pension to make sure the difference value is right or not,
        finding out the value of Actural results of Salary is included.

        Is it because of there is no relationship about AccountName between table A and B?