I need a Help using DAX I wanted Equally Split Sold Qty in each month within pooled Territory

[VikcyD]

I have a requirement where the expected output is to divide any quantity sold in a pooled city into percentage ratios, and then add these ratios for each month, day, or quarter. I would appreciate any insights or solutions on how to achieve this using DAX / PQ

[VikcyD]

@Zang_Mi  It seems there’s an issue with the formula. When I apply the same formula, it only multiplies the value for the respective HQ by the percentage, instead of summing the total and then multiplying. Please Help 

Data Model : 

[VikcyD]

Thanks for the PQ Solution, it's really helpful and I appreciate this kind of support. Would it be possible to solve it using DAX as well?

[Zang_Mi]

Hello @VikcyD, the solution I have provided is achieved with DAX (creating a calculated column) 🙂.

Regards

[Zang_Mi · Accepted solution]

Hello, this is an example with the result you are looking for. I've used DAX to do the calculation. 

First, I create two queries to load the data I need.

Pooled City (% of split per city)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcknNychU0lEyNlBVitWJVnIvLUpPzM9DEvFILKpMzEsEipiARGIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Pooled City" = _t, #"% of Split" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pooled City", type text}, {"% of Split", Percentage.Type}})
in
    #"Changed Type"

Quantity (quantity per month and city)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JR8i3NTUrMBDIMDQwMlGJ1YOIuqTkZYGEjFGH30qL0xPw8iHokcY/EosrEvEQgCyzolpqE1WyIONxsCxRhhNlIgsgGxwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, City = _t, Qty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type text}, {"City", type text}, {"Qty", Int64.Type}})
in
    #"Changed Type"

Then, I related two tables as shown belong (Pooled City -> City)

Finally, I create a calculated column with this expression in Quantity table.

Final output = 
VAR __SPLIT_PCT = RELATED('Pooled City'[% of Split])
VAR __POOLED_QTY = CALCULATE(SUMX(Quantity, Quantity[Qty]), KEEPFILTERS(FILTER(Quantity, RELATED('Pooled City'[% of Split]) <> BLANK())), ALLEXCEPT(Quantity, Quantity[Month]))
RETURN IF(ISBLANK(__SPLIT_PCT), Quantity[Qty], __SPLIT_PCT*__POOLED_QTY)

How the measure obtain the correct result:

1. __SPLIT_PCT. Obtain for each row (city), the % split from another table, it returns blank if the city is not a pooled city.
2. __POOLED_QTY. This variable return, within the given month, the sum of pooled city for each pooled city. It returns blank if the city is not a pooled city.
3. In the last step, we apply the logic: if it is not a pooled city, we just take the value from Quantity column, otherwise, it should be the product of sum of pooled city and % split.

If this answer helps you, please give a kudo and mark it as solution 🙂.