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
    Icon for Community Champion rankCommunity 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
        Icon for Community Champion rankCommunity 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