Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi all.
I'm trying to emulate the below in Power BI;
It shows the total cumalative % of queries received based on a banded column value for the last 3 years as well as the current month (the current month is not important at the moment, just the years element).
So, in 2022, just over 50% of all queries were logged within 0-7 Days, just under 70% of all queries were logged within 0-14 days, just over 70% of all queries were logged within 0-21 days - and so on and so forth - until we see that 100% of all queries for 2022 were logged within the 0 - 61+ Days bands.
Can anyone advise on how can make this possible with a measure?
Solved! Go to Solution.
Hi @Jaqinthebox ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
In power query ,copy this code in advanced editor
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddAxDoMwDIXhu0TqBJZsxzFh7y0QQ8/QidvTIYBflG5RPtn5lW1LyqppTkzL+3N8f6fCr7TPN1QSayIZRAqpXIRDqqT12oeykpXxkDk5NzIQl6nd1/s+Q7QzQIjOALEZd8VkRxkn5y4ZH3qSw4j9+2jrPlpAxtHWRRtKjMZ1MdpBuuj9BA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, #"Queries Days" = _t, Percentage = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Queries Days", type text}, {"Percentage", Percentage.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Year"}, {{"Count", each _, type table [Year=nullable number, Queries Days=nullable text, Percentage=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Queries Days", "Percentage", "Index"}, {"Custom.Queries Days", "Custom.Percentage", "Custom.Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom.Percentage", Percentage.Type}})
in
#"Changed Type1"
Close and apply and create a Column
Result =
CALCULATE(
SUM('Table'[Custom.Percentage]),
FILTER(
ALLEXCEPT(
'Table',
'Table'[Year]
),
'Table'[Custom.Index] <= EARLIER('Table'[Custom.Index])
)
)
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Jaqinthebox ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:
In power query ,copy this code in advanced editor
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddAxDoMwDIXhu0TqBJZsxzFh7y0QQ8/QidvTIYBflG5RPtn5lW1LyqppTkzL+3N8f6fCr7TPN1QSayIZRAqpXIRDqqT12oeykpXxkDk5NzIQl6nd1/s+Q7QzQIjOALEZd8VkRxkn5y4ZH3qSw4j9+2jrPlpAxtHWRRtKjMZ1MdpBuuj9BA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, #"Queries Days" = _t, Percentage = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Queries Days", type text}, {"Percentage", Percentage.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Year"}, {{"Count", each _, type table [Year=nullable number, Queries Days=nullable text, Percentage=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Queries Days", "Percentage", "Index"}, {"Custom.Queries Days", "Custom.Percentage", "Custom.Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Custom.Percentage", Percentage.Type}})
in
#"Changed Type1"
Close and apply and create a Column
Result =
CALCULATE(
SUM('Table'[Custom.Percentage]),
FILTER(
ALLEXCEPT(
'Table',
'Table'[Year]
),
'Table'[Custom.Index] <= EARLIER('Table'[Custom.Index])
)
)
Final output
Best regards,
Albert He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
User | Count |
---|---|
77 | |
75 | |
67 | |
65 | |
47 |
User | Count |
---|---|
108 | |
106 | |
93 | |
84 | |
66 |