Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Blank Measure Total

I have a model that includes three measures - all working correctly except no total appears. I want to show a cumulative total in a card visual.

Measure 1 (simple revenue sum with some parameters):

 

BRR = 
CALCULATE(
    SUM( 'NETWORK EXPANSIONS REVENUE'[USD_SPOT_AMT_GEOGRAPHIC] )
    , 'NETWORK EXPANSIONS REVENUE'[GL_REVENUE_CATEGORY_CD] = "BRR"
    || 'NETWORK EXPANSIONS REVENUE'[GL_REVENUE_CATEGORY_CD] = "Billable Run Rate"
    || 'NETWORK EXPANSIONS REVENUE'[GL_REVENUE_CATEGORY_CD] = "USGCRD"
    || 'NETWORK EXPANSIONS REVENUE'[GL_REVENUE_CATEGORY_CD] = "USG"
    || 'NETWORK EXPANSIONS REVENUE'[GL_REVENUE_CATEGORY_CD] = "MRRCRD"
    || 'NETWORK EXPANSIONS REVENUE'[GL_REVENUE_CATEGORY_CD] = "MRR Credits"
    || 'NETWORK EXPANSIONS REVENUE'[GL_REVENUE_CATEGORY_CD] = "MRRPRR")

 


Measure 2 (summing the revenue for a three-month period before the project went live and then averaging it for that same 3 month period):

 

PRE-RFS AVERAGE BRR = 
CALCULATE(
    [BRR]
    , DATESINPERIOD( 'CALENDAR'[Date] , EDATE( MIN( 'NETWORK EXPANSIONS PROJECTS'[RFS.DATE] ), -2 ) , -3 , MONTH )
)
/3

 

 
Measure 3 (subtracting the 3-month average from the monthly revenue provided that the date is after the project live date):

 

BRR VS PRE-RFS AVERAGE = 
IF(
    MAX( 'NETWORK EXPANSIONS PROJECTS'[RFS.IND] ) = "No"
    , BLANK()
    , IF(
        MAX( 'CALENDAR'[Date] ) > TODAY()
        , BLANK()
    , IF(
        MAX( 'CALENDAR'[Date] ) < MAX( 'NETWORK EXPANSIONS PROJECTS'[RFS.DATE] )
        , BLANK()
        , [BRR] - [PRE-RFS AVERAGE BRR]
    )
))

 

  
I have an area chart that plots the result of measure 3 correctly but I want to sum up all of the values into a cumulative total and present in a card visual.

The total of Measure 3, however, is blank and I can't work out how to fix it.

Here is a link to a sample file: 

https://www.dropbox.com/s/h3kbeqwxeet2qcx/Power%20BI%20Issue.pbix?dl=0

Thanks,
Jake

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Because you have used the row context. Based on the visual group by you need to use values/summarize to get grand total

    Sumx(

    Summarize('NETWORK EXPANSIONS PROJECTS', 'NETWORK EXPANSIONS PROJECTS'[RFS IND] ,"_new",[BRR VS PRE-RFS AVERAGE]) ,[_new] )

    https://www.youtube.com/watch?v=ufHOOLdi_jk

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak,

      Thanks for the response. 

      I'm finding that both your suggested DAX and the solution from the YouTube link you provided are both not seeming to work for me.

      BRR VS PRE-RFS AVERAGE TOTAL = 
SUMX(
    SUMMARIZE('NETWORK EXPANSIONS PROJECTS','NETWORK EXPANSIONS PROJECTS'[RFS.IND], "_new", [BRR VS PRE-RFS AVERAGE])
    , [_new])


      Result (no total):


      YouTube solution:

      BRR VS PRE-RFS AVERAGE TOTAL = 
IF(
    HASONEVALUE('CALENDAR'[DATE])
    ,[BRR VS PRE-RFS AVERAGE]
    ,SUMX(VALUES('CALENDAR'[DATE]), [BRR VS PRE-RFS AVERAGE])
)


      Result (wrong values):















      My sample file is attached if it helps at all:
      https://www.dropbox.com/s/h3kbeqwxeet2qcx/Power%20BI%20Issue.pbix?dl=0


      Thanks for the assistance,
      Jake

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous 

        I suggest you to add a year and a month column in calendar table instead of using time intelligence. Then try this code.

        BRR VS PRE-RFS AVERAGE TOTAL = 
SUMX(
    SUMMARIZE('CALENDAR','CALENDAR'[Year],'CALENDAR'[Month], "_new", [BRR VS PRE-RFS AVERAGE])
    , [_new])

        Result:

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.