Calculate the difference between 2 columns in 2 separate tables

[toni14]

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 ?

[v-juanli-msft]

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

[toni14]

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 ?

[toni14]

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). 

[toni14]

[v-juanli-msft]

Hi @toni14

Relationship should be as below

Best Regards

Maggie

[toni14]

Thank You very much for your reply I solved this earlier 🙂

[toni14]

Hi,

I created a graph that shows conditions of eleven month and graph that shows conditions of twelve month. Picture bellow.

I want that data label shows me a value option where I can clearly see how much hours, minutes and seconds for each user.

 

[v-juanli-msft]

Hi @toni14

You could turn on the "data label" bar on the visual format pane.

If this doesn't help, please share which columns or measures added in the visual and which data you want to show.

Since your previous problem is sloved, It is better for you to post another to get more helps

Best Regards

Maggie 

“ Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
“

[toni14]

Hi,

I know i turn on data label but informations that i want isn't show on graph.

To me the most important thing to show up is a column "Value" where I will see everything i need on graph.

[toni14]

[v-juanli-msft]

Hi @toni14

when i turn on the data label bar, it shows as below

However, it seems you want to show value like "**h**m**s" shown on the visual, right?

i'm afraid it doesn't support to display this currently.

for a workaround, you could add [sub] in a card visual on another page, then make this page as a tooltip, called page3

next, turn on "tooltip" for your visual, select "report page", use the page3

More details

create tooltips based on report pages.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[toni14]

Hi,

Thank you for your reply.

I want to show a Value from tables i import on the beginning in the place that shows theese (0,31 M, 0,60M...). I will send you a screenshot again.

[v-juanli-msft]

Hi @toni14

Just change the column used in the "report tooltip", then you can see values from this column.

Best Regards

Maggie

[v-juanli-msft]

Hi @toni14

Sorry for not completing my solution, i am working on this problem.

i will come back as soon as i figure out it.

Best Regards

Maggie

[v-juanli-msft]

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