Forum Discussion

bsas's avatar
bsas
Icon for Post Patron rankPost Patron
8 years ago
Solved

Fill missing months rows with 0 or value in running total measure for correct KPI visualization

Hi All,

 

I have some sales data per month and goal per month. I need to create KPI view. The problem is that for some months I do not have any sales data but do have goal and when I'm trying to create some measures I receive wrong data.

 

running total for sales per month

 

WonRT = CALCULATE(SUM(opportunity[Closed_booking]),
         FILTER(ALLSELECTED(opportunity),MAX(opportunity[ss_closedate]) >= opportunity[ss_closedate]))

running total for goal per month

 

goalRT = CALCULATE(SUM('territory goals'[booking_goal]),
          FILTER(ALLSELECTED(opportunity),MAX(opportunity[ss_closedate]) >= opportunity[ss_closedate]))

I have problem with blank data. How I can fill data in first column as 0 for "red" months and with goal (it is present in other table) with value?

 

Need Help
Show and Tell
Tips and Tricks
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi bsas,

    Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will find workaround quickly and easily. If you haven't, please feel free to ask.

    Thanks,
    Angelia

5 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi bsas,

    Please add 0 at the ending of your formula and check if it works fine, please review the following formulas.

    WonRT =
CALCULATE (
    SUM ( opportunity[Closed_booking] ),
    FILTER (
        ALLSELECTED ( opportunity ),
        MAX ( opportunity[ss_closedate] ) >= opportunity[ss_closedate]
    )
)
    + 0

     

    goalRT =
CALCULATE (
    SUM ( 'territory goals'[booking_goal] ),
    FILTER (
        ALLSELECTED ( opportunity ),
        MAX ( opportunity[ss_closedate] ) >= opportunity[ss_closedate]
    )
)
    + 0


    Please respond to me if you have any other problems.

    Best Regards,
    Angelia

    • bsas's avatar
      bsas
      Icon for Post Patron rankPost Patron
      8 years ago

      Hi v-huizhn-msft,

       

      Tahnks for your efforts! I've checked, adding 0 do not work, it gives same result.

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi @bsas,

        Please create two measure using the formulas below, and check if it works fine.

        WonRT_new =IF(ISBLANK([WonRT]),0,[WonRT])+0

goalRT_new =IF(ISBLANK([goalRT]),0,[goalRT])+0


        Best Regards,
        Angelia