Forum Discussion
Monthly Total in Total Row
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!
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.
2 Replies
- lbendlin
Super User
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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523 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.
