Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

SUMX Measure referencing another Measure

Hi all,

I am writing a SUMX + IF formula that references another formula and the results are just slightly different than they're supposed to be.

 

Screenshot of table visual

 


Measures

 

Measure = 
CALCULATE(
SUM( REVENUE[USD_BUDGET_AMT] )
, REVENUE[GL_REVENUE_CATEGORY_CD] = "BRR"
|| REVENUE[GL_REVENUE_CATEGORY_CD] = "Billable Run Rate"
)
Measure 2 = 
CALCULATE(
[Measure]
, REVENUE[GL_SEG4_TIER2_DESC] = "Wavelengths"
|| REVENUE[GL_SEG4_TIER2_DESC] = "IP"
|| REVENUE[GL_SEG4_TIER2_DESC] = "Private Line"
|| REVENUE[GL_SEG4_TIER2_DESC] = "Dark Fiber"
)
Measure 3 = 
CALCULATE(
[Measure]
, REVENUE[GL_SEG4_TIER2_DESC] = "Colocation and Data Center"
)

 

Measure 4 = 
SUMX(
REVENUE
, IF(
REVENUE[PROJECT] = "Hamburg Colo2 Expansion"
, [Measure 3]
, [Measure 2]
)
)


I am trying to make 'Measure 4' return the same values as 'Measure 2' (or 'Measure' for each of the product lines that are filtered in 'Measure 2') but for some reason 'Measure 4' is nearly always slightly higher than 'Measure'/'Measure 2' and after many hours of forum searching and keyboard smashing, I am finally defeated.

Any ideas?

Data model in case it ends up being relevant:

 
 


Thanks

  • Measure 4 = 
    SUMX( 
    VALUES(REVENUE[PROJECT])
    , IF(
    REVENUE[PROJECT] = "Hamburg Colo2 Expansion"
    , [Measure 3]
    , [Measure 2]
    )
    )

2 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    Measure 4 = 
    SUMX( 
    VALUES(REVENUE[PROJECT])
    , IF(
    REVENUE[PROJECT] = "Hamburg Colo2 Expansion"
    , [Measure 3]
    , [Measure 2]
    )
    )
  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion

    Hi Anonymous 

    Why would you expect them to reurn the same values?

    Can you share the pbix?

    Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

     

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

    Cheers