Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ei...
data protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Icey's avatar
    Icey
    6 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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: 

 

  1. Main Table:
  2. Monthly Quota Table:

  3. 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.

 

Icey's avatar
Icey
Icon for Community Support rankCommunity Support
6 years ago

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.