topic Re: Monthly Total in Total Row in DAX Commands and Tips

Monthly Total in Total Row

Monocot — Thu, 13 Feb 2025 18:20:16 GMT

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!

Re: Monthly Total in Total Row

lbendlin — Fri, 14 Feb 2025 01:59:08 GMT

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

Re: Monthly Total in Total Row

Anonymous — Fri, 14 Feb 2025 02:19:30 GMT

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.