Skip to main content
cancel
Showing results for 
Search instead 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

Reply
mhr08004
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 NumberCost CategoryBudget CostActual CostPct of CostHas Cost in Cat 2?
xTotal Job1020=NA= IF CAT2 Budget > 0, Yes, No
xCat 112=Cat 1 Budget / Total Budget NA
xCat 212NA
xCat 312NA
xCat 412..NA
xCat 512NA
xCat 612NA
xCat 712 NA
xCat 812 NA
xCat 912 NA
xCat 1012 NA
2 ACCEPTED SOLUTIONS
amitchandak
Super User
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")

View solution in original post

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

View solution in original post

4 REPLIES 4
Rajanaidu
New Member

Hi Amit

amitchandak
Super User
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")

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors