topic Re: Measure that Sums and Finds Value in Another Table with A Range in DAX Commands and Tips

Measure that Sums and Finds Value in Another Table with A Range

AMPAllie — Wed, 07 Oct 2020 14:43:52 GMT

I have two tables

Table 1 lists sales

Table 2 is a commission struture

I have a measure that sums Table1[Amount] placed in a gauge with Year, month, Sales Rep slicers.

What I need to accomplish is a measure in another guage that determines the commission amount.

So i need to create something that sums Table1.[Total], then takes that value and looks to see into which range in Table2 that value falls, and returns the corresponding Table2.[Commission].

I have attempted to break up the ranges in Power Query with ={[Range Start]..[Range End]} and list all values to more easily find a corresponding value, but I end up with a table that has over 25M rows which is inefficient.

Any help is much appreciated.

Re: Measure that Sums and Finds Value in Another Table with A Range

amitchandak — Wed, 07 Oct 2020 15:06:06 GMT

AMPAllie ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Measure that Sums and Finds Value in Another Table with A Range

AMPAllie — Wed, 07 Oct 2020 15:39:27 GMT

Table 1

WBS1	InvoiceNumber	Period	TransDate	InvoiceDate	TotalAmt	PR.Name	PR.ProjectCustomTabFields.Sales Rep	PR.Project Type	Amount
19028.00.00	000000082142	202001	1/25/2020 12:00:00 AM	2019-12-04T12:05:31.5270000	-18219	PG&E EGI Reconductor Projects	Person 1	Engineering	18219
19028.00.00	000000082265	202002	2/12/2020 12:00:00 AM	2/12/2020 12:00:00 AM	-46339.39	PG&E EGI Reconductor Projects	Person 2	Engineering	46339.39
19028.00.00	000000082365	202007	7/11/2020 12:00:00 AM	4/2/2020 12:00:00 AM	-89113.33	PG&E EGI Reconductor Projects	Person 3	Engineering	89113.33

Table 2

Range Start	Range End	Commission
5000000	9999999	10000
10000000	14999999	15000
15000000	19999999	20000
20000000	24999999	25000
25000000	30000000	30000

Output would just be a number

Re: Measure that Sums and Finds Value in Another Table with A Range

AMPAllie — Wed, 07 Oct 2020 15:40:02 GMT

It's being a buggar with uploading

Re: Measure that Sums and Finds Value in Another Table with A Range

AMPAllie — Wed, 07 Oct 2020 15:42:58 GMT

Let's try this

Table 1

Table 2