cancel
Showing results for
Did you mean:
Frequent Visitor

## PQ merge comission data within a range of numbers

Hi forum,

Im facing a challenge to solve regarding the calculation of sales comissions.

The comission is depending on a discount for sold items.

I have 2 tables:

1. DimComission

This table contains the [DiscountUpperBoundary%] & [Comission%].

[DiscountUpperBoundary%]             [Comission%]

 0% 10% 3% 10% 5% 10% 10% 10% 15% 9% 17% 8% 20% 6% 25% 4% 30% 0%

As you can see, the more discount a salesman grants to the customer, the less comission the salesman gets.

2. FactComission

This table containts the real sales data from an ERP system for a lot of orders.

This is just a unique extracts of them:

[ActualDiscount%]

0,00 %
1,00 %
5,00 %
6,00 %
10,00 %
12,00 %
14,50 %
15,40 %
16,00 %
18,00 %
19,00 %
20,00 %
23,00 %
23,50 %
25,00 %
26,00 %
28,00 %
29,00 %
30,00 %
32,50 %

My question: How can i merge the Fact table with the ranges of the Dimensions dynamically?

For example, [ActualDiscount%] 6,00 % is not in the DimComission table so 10% is to be taken, as its the next highest [DiscountUpperBoundary%].

Could this be solved maybe with a list in PQ or are there other solutions?

Thanks for any help and advice

Kind regards,
DataHero

1 ACCEPTED SOLUTION
Super User

Hi @DataHero ,

Yes, I think you first need to get the two columns DiscountLowerBoundary and DiscountUpperBoundary in your DimComission table:

Here the M code on how to do this:

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlBV0lEyBJKxOtFKxsgcU2SOIYo6Q7CcJZRjDuJYQDhGYHVmUA5YmQnUbLAMyIBYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DiscountUpperBoundary = _t, Comission = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DiscountUpperBoundary", Percentage.Type}, {"Comission", Percentage.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"DiscountUpperBoundary", "DiscountLowerBoundary"}}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index1",{"Index", "Index.1"})
in
#"Removed Columns"```

With them in place, you should be able to get the matches as described in the link:

Here the M code:

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7BCcBACETRXoTcgphxDUktsv23kUPQ8fZA/ZgppmaH7DPlakXr5nQsglwaxdBVHGcP+TbBGHyyYuALYAyMgTFnzPEX9gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ActualDiscount = _t]),
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"ActualDiscount", Percentage.Type}}),
(F) =>  Table.SelectRows(DimComission,
(D)=> D[DiscountUpperBoundary] > F[ActualDiscount] and D[DiscountLowerBoundary] <= F[ActualDiscount] )
),
#"Expanded Comission" = Table.ExpandTableColumn(Merge, "Comission", {"Comission"}, {"Comission"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Comission",null,0,Replacer.ReplaceValue,{"Comission"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Comission", Percentage.Type}})
in
#"Changed Type"```

Was it this you were looking for? 🙂

Let me know!

Proud to be a Super User!

6 REPLIES 6
Solution Sage

Hi

Add a new column in FactComission

`try DimComission[#"Comission%"]{List.PositionOf(DimComission[#"DiscountUpperBoundary%"],[#"ActualDiscount%"],1,(x,y)=>x<y)+1} otherwise 0`

Stéphane

Frequent Visitor

What is it doing specifically?

Super User

Hi @DataHero ,

Have a look into this blog post. I think they did something similar there:

Merging with date range using Power Query - Exceed

Let me know if this helps 🙂

Proud to be a Super User!

Frequent Visitor

Hi @tackytechtom thank you very much for the link.

I tried that solution and it works partially.

I think the problem is that I dont have ranges in the DimComission table.

There is the upper value only but nothing like 0-3%.
And its very time consuming to change this manually in the data source.

Do you know, how I can implement a proper range with 2 columns in Power Query?

Kind regards,
DataHero

Super User

Hi @DataHero ,

Yes, I think you first need to get the two columns DiscountLowerBoundary and DiscountUpperBoundary in your DimComission table:

Here the M code on how to do this:

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlBV0lEyBJKxOtFKxsgcU2SOIYo6Q7CcJZRjDuJYQDhGYHVmUA5YmQnUbLAMyIBYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DiscountUpperBoundary = _t, Comission = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DiscountUpperBoundary", Percentage.Type}, {"Comission", Percentage.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"DiscountUpperBoundary", "DiscountLowerBoundary"}}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index1",{"Index", "Index.1"})
in
#"Removed Columns"```

With them in place, you should be able to get the matches as described in the link:

Here the M code:

```let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7BCcBACETRXoTcgphxDUktsv23kUPQ8fZA/ZgppmaH7DPlakXr5nQsglwaxdBVHGcP+TbBGHyyYuALYAyMgTFnzPEX9gc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ActualDiscount = _t]),
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"ActualDiscount", Percentage.Type}}),
(F) =>  Table.SelectRows(DimComission,
(D)=> D[DiscountUpperBoundary] > F[ActualDiscount] and D[DiscountLowerBoundary] <= F[ActualDiscount] )
),
#"Expanded Comission" = Table.ExpandTableColumn(Merge, "Comission", {"Comission"}, {"Comission"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded Comission",null,0,Replacer.ReplaceValue,{"Comission"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Comission", Percentage.Type}})
in
#"Changed Type"```

Was it this you were looking for? 🙂

Let me know!

Proud to be a Super User!

Frequent Visitor

This is really impressive!
Thank you so much for your help!

Kind regards,
DataHero

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors