Hi, I have something similar. I want to show difference between this two table on a month with graphics preview. In this case I want to show the difrerence for each parameters graphically (visual). Can you help me ?

Hi Mr. Juanli, Thank You very much for your reply, I'll try this. :) I Have last question I hope so. How can I show visual theese two tables in a way that woould be graph options where I can see deviations ?

I have tryed Your solution and I have a little problem. Parameters in column second2 are wrong. Second2 column show me the same value in every row (341.648,00).

Re: Calculate the difference between 2 columns in 2 separate tables

15 Replies

v-juanli-msft

Community Support

7 years ago

Hi toni14

Open edit Queries,

in Table1, select "Total active hours", "Total inactive hours","Total hours logged on" columns and unpivot them.

then merge columns "User" and "Attribute" with separator "space".

The same done in the Table2

Close&&apply

create a relationship between Table1 and Table2 based on merged column

create measures in Table1 and Table2

in Table1

seconds1 =
VAR h =
    SEARCH ( "h", MAX ( Table1[Value] ), 1, 0 )
VAR m1 =
    LEFT ( MAX ( Table1[Value] ), h - 1 )
VAR m =
    SEARCH ( "m", MAX ( Table1[Value] ), 1, 0 )
VAR m2 =
    MID ( MAX ( Table1[Value] ), h + 2, m - ( h + 2 ) )
VAR s =
    SEARCH ( "s", MAX ( Table1[Value] ), 1, 0 )
VAR m3 =
    MID ( MAX ( Table1[Value] ), m + 2, s - ( m + 2 ) )
RETURN
    m1 * 60
        * 60
        + m2 * 60
        + m3

In Table2

seconds2 =
VAR h =
    SEARCH ( "h", MAX ( Table2[Value] ), 1, 0 )
VAR m1 =
    LEFT ( MAX ( Table2[Value] ), h - 1 )
VAR m =
    SEARCH ( "m", MAX ( Table2[Value] ), 1, 0 )
VAR m2 =
    MID ( MAX ( Table2[Value] ), h + 2, m - ( h + 2 ) )
VAR s =
    SEARCH ( "s", MAX ( Table2[Value] ), 1, 0 )
VAR m3 =
    MID ( MAX ( Table2[Value] ), m + 2, s - ( m + 2 ) )
RETURN
    m1 * 60
        * 60
        + m2 * 60
        + m3

Then create measures in Table1

sub = [seconds1]-[seconds2]

h_m_s =
INT ( [sub] / 3600 )
    & "h "
    & INT ( ( [sub] - INT ( [sub] / 3600 ) * 3600 ) / 60 )
    & "m "
    & MOD ( [sub], 60 )
    & "s"

Best reagrds

Maggie

Calculate the difference.pbix57 KB