User Profile
Zang_Mi
Resolver II
Joined 2 years ago
User Widgets
Contributions
Re: Not possible to convert a cell in a duration
Hello, if the duration can be over 24 hours, then you need to transform the text to the format d.hh:mm:ss before converting it to duration data type. For example: from 39:38:58 to 1.15:38:58 M Query with the example and steps to apply: let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMra0MrawMrVQitWJVjI0tTIwsjIxVYqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t]), #"Added Custom" = Table.AddColumn(Source, "Duration Format", each let hours_to_days = Number.FromText(Text.BeforeDelimiter([Duration], ":"))/24, days = Number.RoundDown(hours_to_days), hours = (hours_to_days-days)*24, minutes_and_seconds = Text.AfterDelimiter([Duration], ":"), duration_text = Number.ToText(days) & "." & Number.ToText(hours) & ":" & minutes_and_seconds in duration_text), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Duration Format", type duration}}) in #"Changed Type" If this answer helps you, please give a kudo and mark it as solution 🙂.Re: Measure groupby text column
Hello, regarding your question, I would say it is more efficient having a unique measure for that purpose while we can use days_group as a legend. Let's assume that we create a matrix using days_group as columns (or legend in bar chart), and we can see how the cumulative sum changes each month. Theres is a DAX expression to the solution (you can adjust it as needed). Note: test_date_num is the a numeric value that represents month. Cumulative sum = VAR __START_MONTH = CALCULATE(MIN(test[test_date_num]), REMOVEFILTERS(test[test_date])) VAR __END_MONTH = MAX(test[test_date_num]) VAR __RESULT = CALCULATE(SUM(test[test_amount]), REMOVEFILTERS(test[test_date]), test[test_date_num] >= __START_MONTH && test[test_date_num] <= __END_MONTH) RETURN __RESULT __START_MONTH. The first month selected per each days_group. In our case, it is always the Jan. __END_MONTH. The current month used as axis, it can be January or any month. __RESULT. Calculate per each month displayed in the axis, the cumulative sum from the first month to the current month. 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
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: Progressive Difference Calculation
Hello, I've got the expected result in a matrix using one measure. In that matrix, customer, date, and hour are used as row hierarchy, and in the column of total income we can see the income value at each hour. M query used to build this sample data: let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1SMFTSUTKy1Dcw1jcyMDIBcgzMrAwMgLShAZCK1cGn1NAEotTSlKBKC6ihRtiUGhjqG5ig22+CzXoUlTDrTQmrhFpvDlIZCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Delivery Date" = _t, Rel.Date = _t, #"Total Income" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Delivery Date", type text}, {"Rel.Date", type time}, {"Total Income", Int64.Type}}) in #"Changed Type" Measure: Difference = VAR __CURRENT_ROW_DELIVERY_HOUR = SELECTEDVALUE('Table'[Rel.Date]) VAR __PREVIOUS_HOUR = CALCULATE(MAX('Table'[Rel.Date]), ALLEXCEPT('Table', 'Table'[Customer], 'Table'[Delivery Date]), 'Table'[Rel.Date] < __CURRENT_ROW_DELIVERY_HOUR) VAR __CURRENT_ROW_INCOME = SUM('Table'[Total Income]) VAR __PREVIOUS_ROW_INCOME = CALCULATE(SUM('Table'[Total Income]), 'Table'[Rel.Date] = __PREVIOUS_HOUR) VAR DIFFERENCE = IF(ISBLANK(__PREVIOUS_ROW_INCOME), BLANK(), __CURRENT_ROW_INCOME - __PREVIOUS_ROW_INCOME) RETURN DIFFERENCE In summary: Obtain the hour of the current row (for current customer and delivery date as filtered by the row) Obtain the hour of the previous row, which should be the maximum hour value that is smaller than the current hour, for the same customer and delivery date. Calculate the income for the current row Calculate the income for the previous row (filtered in the current row by the same customer and delivery date, and we also add an additional filter to take exactly the income value of the previous hour) Calculate the difference (current row income - previous row income) under the condition that, if there is not a previous hour, just return a blank value and no calculation has to be done. If this answer helps you, please give a kudo and mark it as solution 🙂.Re: Measure groupby text column
Hello, I build a small sample based on your description, this is a way to calculate cumulative value per month, hope that can help you to adjust your measure. Measure: measure test_amount ALLSELECTED = VAR CURRENT_MONTH = SELECTEDVALUE(test[test_date]) VAR CURRENT_DAYS_GROUP = SELECTEDVALUE(test[days_group]) RETURN CALCULATE(SUM(test[test_amount]), FILTER(ALLSELECTED(test), test[test_date] = CURRENT_MONTH && test[days_group] <= CURRENT_DAYS_GROUP))
