Forum Discussion
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...
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 |
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")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
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")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")
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.
