Total Value of column created by IF measure from two separate tables

[Anonymous]

I've seen a lot of solutions to this, but none that work for my specific issue.

I have one table (rawdata) that measures hours supplied by a contractor, the work type and location category.

I have a second table (targets) that shows the target hours for each work type and location category. For example, Work type A will then be split into location categories 0, 1, and 2. 0, 1 and 2 will each have their own target.

The table I've created in Power BI lists each work type A - H and each location category, it then shows the associated targets, the actual hours, and the percentage of the hours against the targets, e.g.

Type	Location	Target	Actual	Percent
A	0	100	17	17%
A	1	50	25	50%
A	2	200	50	25%
B	0	100	110	110%
B	1	50	25	50%
B	2	25	50	200%
TOTAL		525	277	53%

What is now required is an additional two columns, one that shows the actual hours minus any hours over the target, and the same for percent. For example, B,2 has a target of 25 hours, but 50 hours is recorded, there for the additional 25 over target should not count towards the overall target (so we do not hit the overall target by missing more difficult work types/locations).

The measure I have used is: 

Actual Hours Minus Excess =

IF(

    sum('rawdata'[Actual]) > sum('targets'[Target]),

    sum('targets'[Target]),

    sum('rawdata'[Actual])

)

So, this works for each row, but as we know the measure is also applied to the total column, rather than summing the values in the column. I have seen many solutions for similar issues but none that work for this measure that uses details from two tables like my situation.

Any help would be really appreciated.

[smpa01]

@Anonymous 

if your have 'Table 1' as following

| Type | Location | Target | Actual |
|------|----------|--------|--------|
| A    | 0        | 100    | 17     |
| A    | 1        | 50     | 25     |
| A    | 2        | 200    | 50     |
| B    | 0        | 100    | 110    |
| B    | 1        | 50     | 25     |
| B    | 2        | 25     | 50     |

the following measure will do the job for you 

Measure = 
VAR _0 =
    SUMX (
        'Table 1',
        VAR _target = 'Table 1'[Target]
        VAR _actual = 'Table 1'[Actual]
        VAR _revisedActual =
            SWITCH ( TRUE (), _actual >= _target, _target, _actual )
        RETURN
            _revisedActual
    )
RETURN
    DIVIDE ( _0, SUM ( 'Table 1'[Target] ) )

[TheoC]

@smpa01 this would work where all data is in a single table however are you able to amend to given the Hours (or "Actuals Hours") are in the Rawdata table and the Target Hours are in the Targets table?  

[smpa01]

@TheoC  I can surely give that a try.

Can you please provide two seperate sample tables / all sample tables involved here for me to work out the rest ?

[TheoC]

@smpa01 you're a legend, mate.  From my understanding, the below is what @Anonymous is dealing with. I've put together a solution however if there's a way of doing it in a single measure, I am sure it would be much easier.  Thanks heaps mate.

[smpa01 · Accepted solution]

@TheoC  to show you how powerful DAX is

let's suppose there are 3 tables  as following

|          dim          |
|------|----------|-----|
| Type | Location | key |
|------|----------|-----|
| A    | 0        | A-0 |
| A    | 1        | A-1 |
| A    | 2        | A-2 |
| B    | 0        | B-0 |
| B    | 1        | B-1 |
| B    | 2        | B-2 |

|                 factActual           |
|------------|----------|--------|-----|
| Type       | Location | Actual | key |
|------------|----------|--------|-----|
| A          | 0        | 17     | A-0 |
| A          | 1        | 25     | A-1 |
| A          | 2        | 50     | A-2 |
| B          | 0        | 110    | B-0 |
| B          | 1        | 25     | B-1 |
| B          | 2        | 50     | B-2 |

|                  factTarget          |
|------------|----------|--------|-----|
| Type       | Location | Target | key |
|------------|----------|--------|-----|
| A          | 0        | 100    | A-0 |
| A          | 1        | 50     | A-1 |
| A          | 2        | 200    | A-2 |
| B          | 0        | 100    | B-0 |
| B          | 1        | 50     | B-1 |
| B          | 2        | 25     | B-2 |

and the data model is following where 'key' bridges all tables

You can still get the desired by building the viz from getting AXIS from dim and by using the following measure

Measure 2 = 
VAR _0 =
    SUMX (
        dim,
        VAR _target = SUMX(RELATEDTABLE(factTarget),factTarget[Target])
        VAR _actual = SUMX(RELATEDTABLE(factActual),factActual[Actual])
        VAR _revisedActual =
            SWITCH ( TRUE (), _actual >= _target, _target, _actual )
        RETURN
            _revisedActual
    )
RETURN
    DIVIDE ( _0, SUM ( factTarget[Target] ) )

You need to adapt this to your scenario

[Anonymous]

@smpa01 thank you SO much, this works perfectly, and your answer has taught me an aspect of DAX I was previously totally unaware of that will unlock so much more functionality.

@TheoC thank you for better explaining my issue and pushing on with this.

[TheoC]

@smpa01 mate, this is insanely awesome! I've done the MCDAA and am an MCT in BI, but I know I've barely scratched the surface of DAX. If there is any course that truly targets DAX that you'd recommend, it would be greatly appreciated! I would love a recommendation that targets variables and advanced DAX if by chance you know of any? Thanks in advance either way! The PBI Community is honestly such a great place to learn from and connect with gifted people that give back to the community , such as yourself! Cheers again mate!

[smpa01]

@TheoC  apologies for the delayed reply. 

I learnt DAX

by participating in Power BI Forum and Stackoverflow becuase I only learn concepts by doing and applying myself to it 

and 

by reflecting on concepts from other people's solutions and cross checking concepts through only SQLBI. 

Marco and Alberto are the absolute authority when it comes to DAX and the way the peel the layers of DAX to make sense is just unbelievable. I religiously follow their blog along with https://radacad.com/ . 

[TheoC]

@smpa01 that is really great to hear! Thanks so much for that insight! Marco and Alberto are brilliant, that is for sure. Their books are very much structured  as you explained in terms of "layers". Stackoverflow sounds really good, too. I will definitely check it out.

I honestly appreciate your time, mate. Thank you again!

[TheoC]

@Anonymous mate, here is the solution:

Create a new calculated column in both tables and create relationship.

 - Work Type & Location = Targets[Work Type] & " " & Targets[Location Code]

Then create calculated column using "LOOKUPVALUE" with Actual Hours:

And then your IF formula as a Calculated Column in the Actuals table:

Convert the Percentage of Actual Hours to %.

[TheoC]

@Anonymous I recommend creating a third calculated column that that uses the logic "If Actual > Target, Target, Actual"  and then use this column in your measure.  This will lead to the max possible Actual being equal to the Target while also limiting any overstatements at the total level. 

Hope this helps 🙂 

[Anonymous]

Thanks Theo, though that appears to be the same as what I've already done with the IF statement? Please let me know if I've misunderstood your response.

[TheoC]

Hi @Anonymous, try it as a Calculated Column to replace the "Actual" column.  So your "Actual" will be "Actual 2" and, as an example, where you have Target = 100 and Actual = 110, the Actual 2 will equal 100... then use Actual 2 in the Percent measure rather than the original Actual column... this will mitigate the issue.  Hope that makes sense.

[TheoC]

@Anonymous outputs will look something like the below:

[Anonymous]

Thank you Theo.

I attempted it as a column but it forces me to use sum for the targets, e.g. 

IF(

    ('rawdata'[Actual]) > sum('targets'[Target]),

    sum('targets'[Target]),

    ('rawdata'[Actual])

)

I have an error if sums are not used.

Because then, the actuals are of course less than the sum of the taregts, it always shows the actual. If I also use sum for the actuals the numbers I get back are massive and incorrect.

I wonder if this is becasue not every work type/location has a target?

[TheoC]

@Anonymous I just put this together in Power BI Desktop.  

Steps are to create two Calculated Columns:

• Actual 2 = IF ( Table1[Actual] > Table1[Target] , Table1[Target] , Table1[Actual] )
• Percent 2 = DIVIDE ( Table1[Actual 2] , Table1[Target] , 0 )
• Add them to a table, use "Average" instead of "Sum" for the two new columns.

One this I did do is change Location to Text.  But given the column is not being used, it shouldn't have any influence.

Let me know how it goes!

[Anonymous]

Unfortunately I get an error for the targets. The targets are in a separate table to the data around the actuals, so I wonder if this might be causing an issue?

I've seen several solutions for similair issues, but they all revolve around the data being in the same table, which I cannot possibly do.

I'll illustarate with examples of the two tables:

rawdata table (the real table has many more columns but I'll keep this basic:

Contractor	Contractor ID	Hours	Location Code	Work Type
Bob	12	55	0	A
Fred	34	12	2	D

Targets

Work Type	Location Code	Target
A	0	100
A	1	50
A	2	75
B	0	25
B	1
B	2	30
C	0	10
C	1
C	2

[TheoC]

@Anonymous okay, makes sense.

Create a Calculated Column in your Targets table using "LOOKUPVALUE" like below (please adjust to match your actual table names and columns):

• Actual = LOOKUPVALUE ( RAWDATA ( HOURS ) , RAW DATA ( WORK TYPE ) , TARGETS ( WORK TYPE ) )

Then you can reperform the earlier steps

[Anonymous]

Thank you for persevering with this @TheoC 

Unfortunately, when I create the new column in the Targets table the resulting rows are #Error with the message A table of multiple values was supplied where a single value was expected

[TheoC]

@Anonymous  big fella, here is the link to the PBIX 

https://axiarcomau-my.sharepoint.com/:u:/g/personal/info_axiar_com_au/Efloyx-Za1dPtroM5J2KLqgBwOlRm6zrerEjqwRbcuXJ_A?e=YtGCAP