Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I am automating our salespeople's commission process and have gotten stuck. Hope you all can help!
This person's contract states that in addition to regular commission, they also get 5% commission on new accounts for the first 120 days after that account's initial order (first sales date). There are certain qualitative criteria that must be met in order to qualify for new account commission, so I will simply tag the account number on the customer table with the person who will receive the new account commission on it. I have a field on the customer table called "New Biz Owner", which I manually populate. I also have measures for [First Sales Date] and [First Sales Date + 120 Days].
My measure for First Sales Date...
First Sales Date = calculate(FIRSTDATE('Sales Document Header'[Posting Date]),FILTER('Sales Document Line','Sales Document Line'[Sales Amount]>0))
and FSD+120..
FSD + 120 = [First Sales Date]+120
To calculate Total Sales from accounts tagged with a specific New Biz Owner (in this case Shane), I simply have:
Shane New Biz Sales = CALCULATE([Posted Sales],'Intl Customers'[New Biz Owner]="Shane")
And to further filter that by only the first 120 days after each account's respective start date:
Shane New Biz 120 = calculate([Shane New Biz Sales],DATESBETWEEN('Posting Date'[Date],[First Sales Date],[FSD + 120]))
All of the measures work, which I have verified in this table above. The Shane New Biz sales calculate and subtotal at $239k, and when I add the DATESBETWEEN filter on Shane New Biz 120, it correctly totals the individual account row in the correct timeframes (each account has a different start date), however, as you can see, there is no subtotal for Shane New Biz 120, which would be the commissionable amount. This causes problems, because if I try to put Shane New Biz 120 onto a card visual, or use in another measure, it shows as (blank)...
I feel like there is some fundamental error in my programming. The next thing I am going to try is creating a calculated column on the customer table for total sales, and then filtering the dates on that into another calculated column, but there has to be an easier way.
Appreciate any feedback, thanks!!
-Brian
Solved! Go to Solution.
Hi,
Try this measure
=SUMX(SUMMARIZE(VALUES(Data[Customer #]),Customer #,"ABCD",[Shane New Biz 120]),[ABCD])
Hope this helps.
Hi,
Try this measure
=SUMX(SUMMARIZE(VALUES(Data[Customer #]),Customer #,"ABCD",[Shane New Biz 120]),[ABCD])
Hope this helps.
When you use a measure, where row context plays a role. Like here the customer of the row will let you know the dates. You need to preserve the same for the grant total
Example
sumx(summarize(table, table[customer #],"_newbiz",calculate([Shane New Biz Sales],DATESBETWEEN('Posting Date'[Date],[First Sales Date],[FSD + 120])),[_newbiz])
You might have to do small changes to make it work.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |