Replace blanks with average values

Question

Hello everybody,I'm quite new to the community, but I hope you guys can help me with the following. I have a metric that contains budget figures for 2025, dimensioned by stores. However, some stores do not have full year data, but only for half of 2025 (since they will open in June or July and thus they don't have budget data before that). I want to create a metric that calculates the full year effect. So in effect I want to replace months with blank values with the averages of the months that do have data. How can I best go about this?Thanks in advance for your help or for pointing me in the right direction!Best, Bas

Min Li · Accepted Answer

​@BasYou can try the formula shown as below:ifblank('15 Source metric','15 Source metric'[remove avg:'15 [Dim] Store']),Where '15 Source metric' is your metric for budget, and'15 [Dim] Store'] is your store dimension

Bas · Answer

Hi ​@Min Li Bright Point: thank you so much for point me in the right direction. I only changed the formula toifblank('15 Source metric','15 Source metric'[remove avg: Month])instead ofifblank('15 Source metric','15 Source metric'[remove avg:'15 [Dim] Store']), and that did the trick😊

Replace blanks with average values

2 replies

Ask your question here!

Ask your question here!

Not finding what you're looking for?

Sign up

Single Sign-On Need help?

Log into the Pigment Community

Single Sign-On Need help?

Scanning file for viruses.

This file cannot be downloaded