cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bsas Member
Member

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?

Untitled.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

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

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 5
v-huizhn-msft Super Contributor
Super Contributor

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

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 Member
Member

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

Hi @v-huizhn-msft,

 

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

v-huizhn-msft Super Contributor
Super Contributor

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

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

v-huizhn-msft Super Contributor
Super Contributor

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

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

Highlighted
bsas Member
Member

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

Thanks Angelina.