CODEWITHSUNDEEP
powerbidaxpowerpivot
Sharingan
Sharingan

Reputation: 333

DAX - Grand Total not adding up to Row Total

I have a powerbi report which is running a dax formula to calculate a custom measure. In the picture below, the total at the bottom doesn't seem to add up to the individual rows. I've been trying my luck for some time and can't seem to figure out why this is.

enter image description here

The DAX formula used is as follows 

SumRest<24hrs7Day = CALCULATE(
                DISTINCTCOUNT(WorkTimeDirective[EmployeeKey]),
                FILTER(
                    ADDCOLUMNS(
                        SUMMARIZE(WorkTimeDirective,Employee[EmployeeKey],'Date'[DateKey]),
                            "totRestHrs", CALCULATE(MAX(WorkTimeDirective[RestHours])
                                ,DATESINPERIOD('Date'[DateKey], LASTDATE('Date'[DateKey]), -7, DAY))
                        ),
                    [totRestHrs]<24
                ),
                WorkTimeDirective[IsEmployeeAbove18]=1
            )

Any idea why this is and what I am doing wrong.

Upvotes: 1

Views: 3259

Answers (1)

Marcus
Marcus

Reputation: 541

For using SUMX the main step is listing the values which you are iterating over, which it typically a table or column. In this case it sounds like you would do a column.

For the example I just had it call the measure you already defined, since breaking DAX calculations into smaller pieces makes writing/testing complex formulas easier.

The idea being that it would iterate over the unique values which are in your TableName[Site Name], then run the [SumRest<24hrs7Day] under that context. I used TableName for the table due to not knowing the name for the table.

SUMX_Example = SUMX( VALUES( TableName[Site Name], [SumRest<24hrs7Day])

Upvotes: 1

Related Questions