Forum Discussion
Filling in for Missing Data?
I have a table that looks like this:
What am I trying to do is some forecasting analytics but I have an issue with missing data. Each month, a user in Salesforce will have an opportunity either created/closed. The trick is they do not have opportunites created/closed in every month. So above, the only month the selected user has data for is 2/2020 and in turn it causes my 'Quoted Revenue' column to be blank for any month that's not February.
The Annual Producer Sum is supposed to be $165,000.24 for every month of the year, but again that is not populated either due to the relationship between my date and main table:
So for the person above, since they only have data for 2/2020 in my main table, the date table picks that up and only populates my 'Quoted Revenue' measure and 'Annual Producer Amount' for that month.
The goal for this is to take my Annual Producer Amount column and multiply it by the far right column to return the 'Quoted Revenue' for that month. That way, in each month in the future I can see exactly how much money they need to make. So for January it should've been 165,000.24 * .065 = $10,725.02. Any ideas on how to get around this issue? I do not want to break the relationship between my date table and my main table.
Hi Anonymous ,
Sorry to reply late. Please try this:
Create measures like so:
Sum of Quota % max per month = MAX('Monthly Quota Table'[Quota %])Annual Quota Measure = SUMX ( ALL ( 'Main Table' ), 'Main Table'[Annual Quota] )* Quoted Revenue = [Annual Quota Measure]*[Sum of Quota % max per month]For detasils, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
You can write measures that will work around these problems, but we will need to know more to advise you on those. Is there any chance you can post a small copy of the power bi file ?
Hi Anonymous ,
Please share me a dummy PBIX file with necessary data (not real data) and measures. Please remove sensitive information.
Best Regards,
Icey
Hi Icey and kentyler , my employer has blocked sites like dropbox/google drive so I am unable to share my .pbix file. It is also hooked into our Salesforce CRM and SQL database so it wouldn't be possible. I will have to do my best explaining via excel sheets that can be replicated:
- Main Table:
- Monthly Quota Table:
- Date Table:
Additional Date Column Needed for Date Table:
Relationship Setups:
Date Table (Date) ---> Main Table (Date) (Single)
Date Table (Month) ---> Monthly Quota Table (Month) (Both)
Current Output:
Expected Output:
So the Annual Quota should be multiplied by the column next to it to give values for the * Quoted Revenue column, but it currently only populates for February since in my main table John Doe only has a February date associated with him.
Hi Anonymous ,
Sorry to reply late. Please try this:
Create measures like so:
Sum of Quota % max per month = MAX('Monthly Quota Table'[Quota %])Annual Quota Measure = SUMX ( ALL ( 'Main Table' ), 'Main Table'[Annual Quota] )* Quoted Revenue = [Annual Quota Measure]*[Sum of Quota % max per month]For detasils, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Main Table:
