Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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

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.

 

TypeLocationTargetActualPercent
A01001717%
A1502550%
A22005025%
B0100110110%
B1502550%
B22550200%
TOTAL 52527753%

 

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

smpa01_0-1632975509964.png

 

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

 

 

smpa01_1-1632975724626.png

 

You need to adapt this to your scenario

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

21 REPLIES 21
smpa01
Super User
Super User

@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] ) )

 

 

smpa01_0-1632969669693.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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?  

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

@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 ?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

 

 

TheoC_0-1632974234670.png

 

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

@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

smpa01_0-1632975509964.png

 

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

 

 

smpa01_1-1632975724626.png

 

You need to adapt this to your scenario

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

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

@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!

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

@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/ . 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

@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!

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

TheoC
Super User
Super User

@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]

 

TheoC_0-1632967924693.png

 

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

 

TheoC_1-1632968052902.png

 

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

 

TheoC_2-1632968087703.png

 

Convert the Percentage of Actual Hours to %.

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

TheoC
Super User
Super User

@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 🙂 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Anonymous
Not applicable

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.

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.

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

@Anonymous outputs will look something like the below:

TheoC_0-1632961165274.png

 

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Anonymous
Not applicable

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?

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

 

TheoC_0-1632965413976.png

 

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.
TheoC_1-1632965531405.png

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!

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Anonymous
Not applicable

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:

ContractorContractor IDHoursLocation CodeWork Type
Bob12550A
Fred34122D

 

Targets

Work TypeLocation CodeTarget
A0100
A150
A275
B025
B1 
B230
C010
C1 
C2 

 

@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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Anonymous
Not applicable

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

@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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.