Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
TejeshRao14188
New Member

divide the sales credit to particular Credit Name

Dear All,

 

Firstly thanks to go through my request. I have Sales data by Customer, by month

Customer Name                                    Sales Qty    HQ Code

AL NOORAIN PHARMACY L.L.C-AL AIN100Abu Dhabi
AL NOORAIN PHARMACY L.L.C-AL AIN100Abu Dhabi
AL NOORAIN PHARMACY L.L.C-AL AIN-100Abu Dhabi
AL NOORAIN PHARMACY L.L.C-AL AIN100Abu Dhabi
AL NOORAIN PHARMACY L.L.C-AL AIN100Abu Dhabi
COUNTRY PHARMACY-KHORFAKHAN7New Area
NEW MEDICAL CENTRE GROUP50Abu Dhabi
NEW MEDICAL CENTRE GROUP50Abu Dhabi
NEW MEDICAL CENTRE GROUP50Abu Dhabi
NEW MEDICAL CENTRE GROUP50Abu Dhabi
NEW MEDICAL CENTRE GROUP100Abu Dhabi
NEW MEDICAL CENTRE GROUP50Abu Dhabi
AL HEERAH DRUG STORE LLC50Sharjah
AL HEERAH DRUG STORE LLC50Sharjah
AL HEERAH DRUG STORE LLC50Sharjah
LIFE GROUP300Life Group
LIFE GROUP300Life Group
LIFE GROUP150Life Group
LIFE GROUP120Life Group

Particular to Customer Name = Life Group Sales Qty to be divided among the Seperate HQ Code i.e; Sharjah & Abu Dhabi

1 ACCEPTED SOLUTION
PijushRoy
Community Champion
Community Champion

Hi @TejeshRao14188 

As per your output, please find the solution and M Code.
Please paste the M COde in your Advance Editor

PijushRoy_0-1701417554931.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1dBPC4IwGAbwr/Kys4oW0vllLieuTZYSIR0WGNalEKKv35tQQQVRYNBhO2y/59mfumaoQBtjMdNQSLQz5EtQgQq4Tzu0yjwWhSHNuD5C0rr1lq28IWP+b497FeOm0qVd3jJ+Lo2dYi7xkpjQ0M0JsGtcz7VYwEwkGadSLigpILWmKsjFz+V/oV/9ymflhKQQFiUktkphXhrySvGrnreu27l2UKuy6f2O4/5JartpIO32x8M3IorfitGjWJ0B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name                                   " = _t, #"Sales Qty" = _t, #"HQ Code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name                                   ", type text}, {"Sales Qty", Int64.Type}, {"HQ Code", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"HQ Code"}, {{"Total Qty", each List.Sum([Sales Qty]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Distribution", each if [HQ Code]="Life Group" then [Total Qty]/2 else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Distribution"}),
    #"Filled Down" = Table.FillDown(#"Filled Up",{"Distribution"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Distribution", type number}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each if [HQ Code]="Abu Dhabi" or [HQ Code]="Sharjah" then [Total Qty]+[Distribution] else if [HQ Code]="Life Group" then "" else [Total Qty]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Distribution"})
in
    #"Removed Columns"

 

 

If solved your requirement, please mark this answer as SOLUTION.
If this comment helps you, appreciate your KUDOS 👍


Thanks
Pijush

Follow www.MyAccountingTricks.com 
Youtube - https://www.youtube.com/channel/UCWWGZXYYq_TmV6UPiC3ujkQ

View solution in original post

4 REPLIES 4
PijushRoy
Community Champion
Community Champion

Hi @TejeshRao14188 

As per your output, please find the solution and M Code.
Please paste the M COde in your Advance Editor

PijushRoy_0-1701417554931.png

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1dBPC4IwGAbwr/Kys4oW0vllLieuTZYSIR0WGNalEKKv35tQQQVRYNBhO2y/59mfumaoQBtjMdNQSLQz5EtQgQq4Tzu0yjwWhSHNuD5C0rr1lq28IWP+b497FeOm0qVd3jJ+Lo2dYi7xkpjQ0M0JsGtcz7VYwEwkGadSLigpILWmKsjFz+V/oV/9ymflhKQQFiUktkphXhrySvGrnreu27l2UKuy6f2O4/5JartpIO32x8M3IorfitGjWJ0B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name                                   " = _t, #"Sales Qty" = _t, #"HQ Code" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name                                   ", type text}, {"Sales Qty", Int64.Type}, {"HQ Code", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"HQ Code"}, {{"Total Qty", each List.Sum([Sales Qty]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Distribution", each if [HQ Code]="Life Group" then [Total Qty]/2 else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Distribution"}),
    #"Filled Down" = Table.FillDown(#"Filled Up",{"Distribution"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Distribution", type number}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom", each if [HQ Code]="Abu Dhabi" or [HQ Code]="Sharjah" then [Total Qty]+[Distribution] else if [HQ Code]="Life Group" then "" else [Total Qty]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type2",{"Distribution"})
in
    #"Removed Columns"

 

 

If solved your requirement, please mark this answer as SOLUTION.
If this comment helps you, appreciate your KUDOS 👍


Thanks
Pijush

Follow www.MyAccountingTricks.com 
Youtube - https://www.youtube.com/channel/UCWWGZXYYq_TmV6UPiC3ujkQ

mussaenda
Community Champion
Community Champion

Hi @TejeshRao14188 ,

 

this is not the best solution but. Just to give you an idea.

mussaenda_0-1701416011418.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1ZBRC4IwEMe/yrFnDS2i52MuJ64tphIhPUwwtJdCiL5+V1C9KFRQ0cPG2P1++9+tLBkq0MZYTDQsJdoF8jWokRpxnyp0yzwWBgHtWB0halzV0pltvA+b/i9CB0xuCp3b9V3zU2nsHFOJF2lGS9cnwK52N0OLFSxElHB6mguSBcTWFEuqT3sj/ksY+KeXI4iTQliUENkihiw3pCjFb0LWuG7nmu/gKpk/+p1cJ1Tttoa42x8P70Ph9Blo3ANtzg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name                                    Sales Qty    HQ Code" = _t, #"Sales Qty" = _t, #"HQ Code" = _t, Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name                                    Sales Qty    HQ Code", type text}, {"Sales Qty", Int64.Type}, {"HQ Code", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"HQ Code", "Column1"}, {{"Sales Qty", each List.Sum([Sales Qty]), type nullable number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Specific Count", each if [HQ Code] = "Abu Dhabi" 
or [HQ Code] = "Sharjah" then 1 else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Life Group Sales Qty", each if [HQ Code] = "Life Group"
then [Sales Qty] else null),
    #"Grouped Rows1" = Table.Group(#"Added Custom1", {"Column1"}, {{"Specific Count", each List.Sum([Specific Count]), type nullable number}, {"Life Group Sales Qty", each List.Sum([Life Group Sales Qty]), type nullable number}, {"All", each _, type table [HQ Code=nullable text, Column1=nullable text, Sales Qty=nullable number, Specific Count=nullable number, Life Group Sales Qty=nullable number]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows1", "All", {"HQ Code", "Sales Qty"}, {"HQ Code", "Sales Qty"}),
    #"Added Custom2" = Table.AddColumn(#"Expanded All", "Grand Total Sales", each if [HQ Code] = "Life Group"
then null 
else

if [HQ Code] = "Abu Dhabi" 
or [HQ Code] = "Sharjah" 
then ([Life Group Sales Qty] / [Specific Count]) + [Sales Qty]
else [Sales Qty]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"HQ Code", "Sales Qty", "Grand Total Sales"})
in
    #"Removed Other Columns"

 

You may have you reasons why you want this in power query, but this is better to be calculated in dax

lbendlin
Super User
Super User


Please show the expected outcome based on the sample data you provided.

Please find the outcome requirement as calculated

TejeshRao14188_0-1701403580457.png

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors