Monthly Total in Total Row

[Monocot]

I am calculating daily sales data by sales rep and comparing that sales to their respective monthly quota. For example, if on January 5, a rep makes a sale of $45,000 and the monthly quota is $100,000, then the Quota % is 45%. However, I need the total line to represent the sum of each rep's monthly quota and not the sum of $100,000 for each day a sale is made. I have made several attempts utilizing SUMX and SUMMARIZE, but I'm not able to quite get it to function. Here is a screen shot of sample data:

Thanks in advance!

[Anonymous · Accepted solution]

Hi @Monocot ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create measures. 

Quota measure = 
var _total=DISTINCTCOUNT('Sales Detail Summary'[Sales Rep])*100000
RETURN IF(ISFILTERED('Sales Detail Summary'[Date]),SUM('Sales Detail Summary'[Quota]),_total)

Quota % measure = 
var _total_quota=DISTINCTCOUNT('Sales Detail Summary'[Sales Rep]) * 100000
var _total_sales=SUM('Sales Detail Summary'[Sales])
RETURN IF(ISFILTERED('Sales Detail Summary'[Date]),MAX('Sales Detail Summary'[Quota %]),DIVIDE(_total_sales,_total_quota))

(3) Then the result is as follows.

If I have misunderstood you please clarify in a follow up reply.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...