Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bsas
Post Patron
Post Patron

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

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

View solution in original post

5 REPLIES 5
v-huizhn-msft
Employee
Employee

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

Hi @v-huizhn-msft,

 

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

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

Thanks Angelina.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.