topic Re: I need a Help using DAX I wanted Equally Split Sold Qty in each month within pooled Territory in DAX Commands and Tips

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

VikcyD — Fri, 29 Mar 2024 18:15:01 GMT

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

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

Zang_Mi — Sat, 30 Mar 2024 11:11:33 GMT

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:

__SPLIT_PCT. Obtain for each row (city), the % split from another table, it returns blank if the city is not a pooled city.
__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.
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 🙂.

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

VikcyD — Sun, 31 Mar 2024 04:10:51 GMT

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?

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

Zang_Mi — Mon, 01 Apr 2024 20:49:07 GMT

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

Regards

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

VikcyD — Thu, 22 Aug 2024 12:14:15 GMT

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 :