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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!