Same Table Calculations and Look-Ups

[mhr08004]

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

I 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

[amitchandak · Accepted solution]

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

[Anonymous]

Hi Amit

[amitchandak · Accepted solution]

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

[mhr08004 · Accepted solution]

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

[mhr08004]

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.