Forum Discussion

JDSang's avatar
JDSang
Frequent Visitor
6 years ago
Solved

SUMIFS in Power Bi

Hi, i'm pretty new to power Bi and am struggling to get my head around tasks that are simple in excel, i have a list of orders each with multiple time entries by different people over various months....
Need Help
  • JDSang's avatar
    JDSang
    6 years ago

    dax edhans 

     

    I seem to have answered my own question. I used:

    Month Hours = CALCULATE(SUM('Table'[Hours]),ALLEXCEPT('Table','Table'[Month]))
    The month hours matches the total for month on the second table.
     
    Thank you for your help 🙂
edhans's avatar
edhans
Community Champion
6 years ago

Sure, but you don't need SUMIFS(). Power BI automatically filters for you based on the table filter connections and the visuals.

See the attached file. It returns a percent of total to help out.

The measure to get percent of total is:

 

Percent of Total = 
VAR CurrentHours = [Total Hours]
VAR TotalHours = 
    SUMX(
        ALL('Table'),
        'Table'[Hours]
    )
VAR Result = 
    DIVIDE(
        CurrentHours,
        TotalHours
    )
RETURN
    Result

 

 

The ALL('Table') tells DAX to ignore the filters on the row, so don't tell me Ann's total, tell me the total of everyone.

If you need further help, please describe the issue in bit more detail, and what your source data looks like with samples.

 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum