Create a cumulative clustered graph by year but counted based on the value of a specific column

[Jaqinthebox]

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?

[v-heq-msft · Accepted 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

[Jaqinthebox]

Thank you @v-heq-msft this solution works perfectly!

Very much appreciated.

Have a great day.

[lbendlin]

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...