Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All, I have quite complicated issue. I have the presented below data table and I need to present the total number of items per value band in stacked area chart. Please note that at each date the number of product items can change and the value band of the product running total can change as well.
The result should look like this, but I do not know how to get there in Power Bi.
Solved! Go to Solution.
Hi @Julian_12 ,
According to your description, you need to use the pivot column function in the power query, and then replace the null values in it. Refer to the following screenshot.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY+xDoAgDET/hZkmbRXRUf0MwqCROOnA/w+CEURJmiaXl7trjRGjkIIwLEYmQALugti9c6ew8uEqcwXIFWdOnMLEsOVYnb/5FNE/3rstwzZ7EXCovFhEc/OpnoNS1emvPXLi4vRbpO5IdYYaqK/Nqiwv/rYX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, PurchaseItem = _t, PurchaseDate = _t, #"Value Band for product" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"PurchaseItem", Int64.Type}, {"PurchaseDate", type date}, {"Value Band for product", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Product"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Value Band for product"]), "Value Band for product", "PurchaseItem"),
#"Sorted Rows1" = Table.Sort(#"Pivoted Column",{{"PurchaseDate", Order.Ascending}}),
#"Replaced Value" = Table.ReplaceValue(#"Sorted Rows1",null,0,Replacer.ReplaceValue,{"green", "amber", "red"})
in
#"Replaced Value"
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Julian_12 ,
According to your description, you need to use the pivot column function in the power query, and then replace the null values in it. Refer to the following screenshot.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY+xDoAgDET/hZkmbRXRUf0MwqCROOnA/w+CEURJmiaXl7trjRGjkIIwLEYmQALugti9c6ew8uEqcwXIFWdOnMLEsOVYnb/5FNE/3rstwzZ7EXCovFhEc/OpnoNS1emvPXLi4vRbpO5IdYYaqK/Nqiwv/rYX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, PurchaseItem = _t, PurchaseDate = _t, #"Value Band for product" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"PurchaseItem", Int64.Type}, {"PurchaseDate", type date}, {"Value Band for product", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Product"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Value Band for product"]), "Value Band for product", "PurchaseItem"),
#"Sorted Rows1" = Table.Sort(#"Pivoted Column",{{"PurchaseDate", Order.Ascending}}),
#"Replaced Value" = Table.ReplaceValue(#"Sorted Rows1",null,0,Replacer.ReplaceValue,{"green", "amber", "red"})
in
#"Replaced Value"
If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-henryk-mstf and @amitchandak
I really tried to make your solution works but I failed. Especially that ‘Value Band per Product’ is calculated in DAX. However, I realised that this problem can be solved in a much simpler way. If I calculate running total per group (see below table), then I can calculate the band values via a measure, which would identify the last row in each group by a date and sum the running total of the identified rows. The ‘Value Band per Product’ would be a legend to separate them on the graph. I believe that I identified the way to calculate it, but I have no idea how to do it in DAX. Would you be able to help please?
Product | No of Purchesed items | Running total per product | Purchese Date | Value band for product |
A | 10 | 10 | 26/01/2021 | green |
A | 15 | 25 | 02/05/2021 | green |
A | 22 | 47 | 10/11/2021 | amber |
B | 2 | 2 | 26/01/2021 | red |
B | 4 | 6 | 09/10/2021 | amber |
B | 0 | 6 | 23/11/2021 | green |
C | 50 | 50 | 26/01/2021 | amber |
C | 12 | 62 | 12/05/2021 | red |
C | 7 | 69 | 18/07/2021 | amber |
C | 15 | 84 | 22/11/2021 | green |
@Julian_12 , The second screenshot seems like a Matrix visual- Date on row, Value brand per item on the column, and no of the purchased item on values.
In case of stacked Area Value brand per item is a legend
Hi @amitchandak, yes, but the real question is how to calculate the second table from the first one.
@Julian_12 , If only want to display matrix would do that ?
Date on row, Value brand per item on the column, and no of the purchased item on values.
Else unpivot the table in power query
https://radacad.com/pivot-and-unpivot-with-power-bi
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |