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

Anonymous
Not applicable

## Struggling to get simple excel statements work

Hi all,

I'm brand new to power BI and have been told by a few people that power bi functionality mirrors excel.  At least from my beginner state, this doesn't appear to be the case. I'm hoping you can help me fix my if statement.  I have a strong suspicion from there i will need help writing a sumif / countif statement as well.

My if ( if([Weighted ABS Workload %]<=0.0349,1,0) ) statement was creating using a new measure (as opposed to new column).  The values are populating correctly at each row, however, for some reason my total isn't populating?  I should expecet to see the value of 10.

thanks,
Jason

1 ACCEPTED SOLUTION
Super User

m_total =

var __table = summarize('Sheet1',[Day],"Measure",[m_single])
RETURN
IF(HASONEVALUE('Sheet1'[Day]),[m_single],SUMX(__table,[Measure])

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
4 REPLIES 4
Super User

@Anonymous ,

Try like sumx(summarize(Table,table[Date],"_sum", if([Weighted ABS Workload %]<=0.0349,1,0) ),[_sum])

Super User

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thanks for the response Greg.

I'm struggling with the formula.  Please keep in mind this is coming from someone who is just beginning to learn python.

I've tried mirroring your formula as follows:

m_single = if([Weighted ABS Workload %] <= 0.0349, 1, 0)

m_total =

var __table = summarize('Sheet1',

as you can see i get stuck when creating our variable __table.  My problem is my formula 'weighted abs workload %' is a measure as opposed to a column, and power BI doesn't appear to recognize measures in your formula?  The underlying formula in weighted abs workload % is ((actual - forecast) / forecast)

Thanks,
Jason

Super User

m_total =

var __table = summarize('Sheet1',[Day],"Measure",[m_single])
RETURN
IF(HASONEVALUE('Sheet1'[Day]),[m_single],SUMX(__table,[Measure])

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors