Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Vishesh Jain]

Hello everyone,

I have a problem for calculating the incentive based on the pre-defined slabs and was hoping if anyone can help me out.

I have a data table which has details about which server has served how many customers on a particular day at which location, which looks like this:

I have created a measure for the total number of customers served which just a sum of the customer column.

Then I have the incentive table which has the lower and upper limits for the total number of customers served and the incentive per customer, which looks like this: (format of the incentive table can be changed)

FR is for Free service type and PMS is for Paid services. When the upper limit is blank, it means anything greater than the lower limit.

The DIM table has the details which server serves which type of customers and looks like this.

The incentive of a free server FR1/2/3 should come from the service type ‘FR’ from the incentive table and those with PMS20/30/60> should come from the PMS server type.

For e.g. Server A2 has serve a total of 85 customers so the incentive slab will be for server type PMS and greater than 51 i.e. 50 per customer.

Here is the desired output:

I would like to have a measure to calculate the Incentive Per Customer based and another measure to multiply the total customers with incentive, which works at the location level as well.
For e.g. For Location A total incentive should be (15*0) + (85*50) = 4250 and NOT (100*50) = 5000

I do not want to hardcode the values in the measure, but I want them to come from the incentive table as the slabs are subject to change.

I hope I have provided all the information and if I am missing anything please do let me know.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————

[Vishesh Jain]

Attaching the sample PBIX.

All the data has been entered into the PBIX manually so that there is no need for source excel files, if you want to change the data.

****EDIT****

In the PBIX in the desired output table, incentive of A2 has been calculated as 25 per customer, please make it 50 since A2 is serving the PMS category.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Imran Ahmed]

forgot to attach screen shot; here it is

——————————
Imran Ahmed
——————————
——————————————-

[Imran Ahmed]

Hi ?

I worked on your problem and i guess I am getting your desired results. I just made one change in your data and put a very high number in target upper limit to make the logic work.

check it out

Thanks

——————————
Imran Ahmed
——————————
——————————————-

[Vishesh Jain]

Hi ,

?Thank you for responding.

I see in your solution you have added another table, to come up with the solution, which will not work as the calculation is fixed now and if I want to see incentive for different months, a calculated table/column will not work in that case.

I will try to use the SUMMARIZE() code inside a measure and see if it works.

****EDIT****

My apologies. The data table does not have the server column. The customers have to be bifurcated based on the server details from the server table.

****EDIT OVER***

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Imran Ahmed]

you can try that but in any case we would need another column to summarize the table on ans it should give you what you are looking for.

——————————
Imran Ahmed
——————————
——————————————-

[Vishesh Jain]

Hi,

I have changed the data model a bit and it looks like this now.

I am attaching my sample file as well, which has the data table without the server name and just the server type.

The total customers measure has the following calculation, in order to bifurcate the customers between them and it is working.

Total Customers =

var server_type1 = SELECTEDVALUE(DIM_Employee[Server Type 1])

var server_type2 = SELECTEDVALUE(DIM_Employee[Server Type 2])

return

IF(

ISINSCOPE(DIM_Employee[Server]),

CALCULATE(SUM(‘Data Table'[Customers]), FILTER(‘Server Type’, ‘Server Type'[Server Type] IN {server_type1, server_type2})),

SUM(‘Data Table'[Customers])

)

I have also figured the incentive per customer using the following formula in my real file, but for some reason the code is not working in the sample file.
Here is the code:

Incentive Per Customer =
var server_type = CALCULATE(
SELECTEDVALUE('Server Type'[Category]),
CROSSFILTER(DIM_Employee[Server Type 1], 'Server Type'[Server Type],
Both))
var customers = [Total Customers]
var Filter_Incentive = FILTER('Incentive Scheme',
customers >= 'Incentive Scheme'[Target Lower Limit] &&
customers <= 'Incentive Scheme'[Target Upper Limit] &&
'Incentive Scheme'[Service Type] = server_type
)
var Incentive = CALCULATE(SELECTEDVALUE('Incentive Scheme'[Incentive Per Customer], Filter_Incentive))
return
Incentive

I am attaching my revised sample file, if anyone would like to have a dig at it.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Vishesh Jain]

Here is a screenshot of the progress of the real report:

The only thing I am missing is filling in the red boxes with the total of 5300 + 4525 + 3760 = 13585

I have already tried SUMX() and it was not working.

Hope someone can help me out.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-

[Vishesh Jain]

Hello everyone,

I think I have got the solution, thanks to from SQLBI.??

I referred to this video, which showed using VALUES() function inside of SUMX() to sum all the visible values and it worked.

https://youtu.be/YtIdcCYnZ9w

I hope if ever someone else has this problem, they can refer to this thread and the video as well.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-