CODEWITHSUNDEEP
dax
Zofia Meyzles
Zofia Meyzles

Reputation: 1

Summing up values

A tabular model. The table 'Historic Project One Hour Value' contains hours value (like price) for working hours on projects on several dates. It is connected only to the project table but not to the date table .

I'm trying to calculate the hours value. For my question let's say there is only one hour on each project. I get the correct answer for each project, but the total is a very big number, which I don't understand.

I will appreciate your help.

One Hour Value On Date :=
VAR OneHourValueOnStartDate =
    FILTER (
        'Historic Project One Hour Value',
        'Historic Project One Hour Value'[HistoricOneHourValueBeginDate]
            = DATE ( 2016, 12, 31 )
    )
VAR OneHourValueOnStartDateForOneProject =
    1
        * /*Actually, I multiply the working hours of the project, but this number is correct */
        CALCULATE (
            SUM ( 'Historic Project One Hour Value'[HistoricOneHourValue] ),
            OneHourValueOnStartDate
        )
RETURN
    IF (
        HASONEVALUE ( Projects[Project ID] ),
        CALCULATE ( OneHourValueOnStartDateForOneProject ),
        CALCULATE (
            SUMX ( VALUES ( Hours[HoursProjectID] ), OneHourValueOnStartDateForOneProject )
        )
    )

Upvotes: 0

Views: 40

Answers (2)

Zofia Meyzles
Zofia Meyzles

Reputation: 1

The correct code after Alexis Olson answer:

One Hour Value On Date:=
VAR OneHourValueOnStartDate =
FILTER (
'Historic Project One Hour Value',
'Historic Project One Hour Value'[HistoricOneHourValueBeginDate]
            = date(2016,12,31)    
)
VAR OneHourValueOnStartDateForOneProject =
1 *   /*Actually, I multiply the working hours of the project, but this number is correct */
CALCULATE (
SUM( 'Historic Project One Hour Value'[HistoricOneHourValue] ),
        OneHourValueOnStartDate
        )
RETURN
IF (
HASONEVALUE ( Projects[Project ID] ),
 CALCULATE ( OneHourValueOnStartDateForOneProject ),
CALCULATE (
  SUMX (
                Values ( Hours[HoursProjectID] ),
                1
      * CALCULATE (
                        MAX ( 'Historic Project One Hour Value'[HistoricOneHourValue] ),
                        FILTER (
                            OneHourValueOnStartDate,
                            'Historic Project One Hour Value'[HistoricOneHourValueProjectID]
                            = EARLIER ( 'Hours'[HoursProjectID] )
                        )
                    )
            )

        )
    )

Thank you

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40204

The variable OneHourValueOnStartDateForOneProject is a constant (not a measure), so what's happening in your total is that this value gives you the sum for all Project ID values in the VAR calculation part.

From there, your SUMX adds that constant value the same number of times as there are distinct HoursProjectID.

Upvotes: 1

Related Questions