cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Same Table Calculations and Look-Ups

I consider myself an Excel expert but the scope of my job is pushing my into BI tools. In Excel, I've done this a million times using various formulas... Xlookups, Sumproducts, Nested IFS... get this done. But BI doesn't work that way... ha

Anyway...

would like perform calculations on a single table. The table contain information on certain jobs and is stacked, 1 line per category, about 50 lines per job.

I'm open to doing this in M or Dax...

Do I make helper queries, Helper columns?

Do I use complex Dax measues?

For reference the table is about 20M rows.

Sample calcs in the last 2 columns with some things we're asked to do.

 Job Number Cost Category Budget Cost Actual Cost Pct of Cost Has Cost in Cat 2? x Total Job 10 20 =NA = IF CAT2 Budget > 0, Yes, No x Cat 1 1 2 =Cat 1 Budget / Total Budget NA x Cat 2 1 2 … NA x Cat 3 1 2 … NA x Cat 4 1 2 .. NA x Cat 5 1 2 … NA x Cat 6 1 2 … NA x Cat 7 1 2 NA x Cat 8 1 2 NA x Cat 9 1 2 NA x Cat 10 1 2 NA
2 ACCEPTED SOLUTIONS
Super User

@mhr08004 , You can try new calculated columns or measures

can be used as a column and measure

Total Job Budget = CALCULATE(SUM('Table'[Budget Cost]), ALLEXCEPT('Table', 'Table'[Job Number]))

new column if Total Job Budget is a column
Pct of Cost = DIVIDE('Table'[Budget Cost], [Total Job Budget])

New measure , if Total Job Budget is meausre
Pct of Cost = DIVIDE(Sum('Table'[Budget Cost]), [Total Job Budget])

a new measure
Has Cost in Cat 2 =
VAR Cat2Budget = CALCULATE(SUM('Table'[Budget Cost]), 'Table'[Cost Category] = "Cat 2")
RETURN
IF(Cat2Budget > 0, "Yes", "No")

Frequent Visitor

Ok... I think I solved the issue. Let me know what you think...

grouping - Power BI DAX : Get sum of a column based on another - Stack Overflow

Total Job Budget = CALCULATE(SUM('Table'[Budget Cost]), ALL('Table' ), 'Table'[Job Number]=EARLIER( 'Table'[Job Number]) &&  'Table'[Cost Cat] <> "Total Job")

4 REPLIES 4
New Member

Hi Amit

Super User

@mhr08004 , You can try new calculated columns or measures

can be used as a column and measure

Total Job Budget = CALCULATE(SUM('Table'[Budget Cost]), ALLEXCEPT('Table', 'Table'[Job Number]))

new column if Total Job Budget is a column
Pct of Cost = DIVIDE('Table'[Budget Cost], [Total Job Budget])

New measure , if Total Job Budget is meausre
Pct of Cost = DIVIDE(Sum('Table'[Budget Cost]), [Total Job Budget])

a new measure
Has Cost in Cat 2 =
VAR Cat2Budget = CALCULATE(SUM('Table'[Budget Cost]), 'Table'[Cost Category] = "Cat 2")
RETURN
IF(Cat2Budget > 0, "Yes", "No")

Frequent Visitor

Ok... I think I solved the issue. Let me know what you think...

grouping - Power BI DAX : Get sum of a column based on another - Stack Overflow

Total Job Budget = CALCULATE(SUM('Table'[Budget Cost]), ALL('Table' ), 'Table'[Job Number]=EARLIER( 'Table'[Job Number]) &&  'Table'[Cost Cat] <> "Total Job")

Frequent Visitor

Thank you -

The helper column:

Total Job Budget = CALCULATE(SUM('Table'[Budget Cost]), ALLEXCEPT('Table', 'Table'[Job Number]))

Doubles the budget because the Total Line that exists in the table as well as the specific categories. How do I exclde that one line from the summation?

Edit: I realize I could just half the result but the logic of including and excluding lines for various measures is important to my further analysis.