The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hello everyone,
I hope all is well.
Kindly need your help with the function below. Able to do it in Excel but not on PBI.
This is the easy option to do it, the more complex one is to the same logic but only compare SKUs bought in a specific date (a column with date is also available). In this case, an SKU can be tail in one year but non-tail in another year.
Any help would be much much appreciated, thank you.
Hi @claudedubois
Please refer to attached sample file with DAX (Measure) solution
Result =
VAR CurrentSKU =
SELECTEDVALUE ( 'Table'[SKU] )
VAR T1 =
ADDCOLUMNS (
ALLSELECTED ( 'Table'[SKU] ),
"@Spend", CALCULATE ( SUM ( 'Table'[Spend] ) )
)
VAR TotalSpend =
SUMX ( T1, [@Spend] )
VAR T2 =
GENERATE (
T1,
VAR CumulativeSpend =
SUMX (
FILTER ( T1, [@Spend] >= EARLIER ( [@Spend] ) ),
[@Spend]
)
VAR CumulativePercentage =
DIVIDE ( CumulativeSpend, TotalSpend )
RETURN
ROW ( "@Percentage", CumulativePercentage )
)
VAR T3 =
FILTER ( T2, [SKU] = CurrentSKU )
VAR CurrentPercentage =
MAXX ( T3, [@Percentage] )
RETURN
IF ( CurrentPercentage > 0.95, "Tail", "Non - Tail" )
I still do not see the data in a format that I can copy. For a solution in Power Query, Place the following M code in a blank query to see the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s0vLU5V0lEyVYrViVbyTq1Myk8sSgEKGBqARXwSC0ryC4B8EwOIQHByUWpqHlDACCrgnJFYlJ5aBBYBCwwBI2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SKU = _t, Spend = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"SKU", type text}, {"Spend", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"SKU"}, {{"Total", each List.Sum([Spend]), type nullable number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Total", Order.Descending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
sumTotal_ = List.Sum(#"Added Index"[Total]),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each List.Sum(List.FirstN(#"Added Index"[Total],[Index])) / sumTotal_, Percentage.Type),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Total", "Index"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Total"}}),
#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Custom", each if [Total] > 0.95 then "Tail" else "Non-Tail", type text),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Total"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Total"}})
in
#"Renamed Columns1"
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
1. Are you looking to do this in power query or in DAX?
2. Can you share the sample data on the left above in text-tabular format instead of in a screen cap, so that it can be copied?
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Hey, thanks a lot for checking my post, the data is below in tabular form.
Regarding DAX vs. power query, I'm not sure as I don't know if you can actually do it in both. I think in DAX it's almost impossible correct? Either method that works is fine.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
20 | |
16 | |
13 |