Help building a cohort matrix percentage visualization

[juliamacg_]

Hello. I'm struggling to create a PBI view of something my colleagues have structured manually on Excel:
1. Sales = a quarter-based table of our company's sales based on their conclusion date.

2. Cancellations = a quarter-based matrix where the rows correspond to the sales conclusion date and the columns correspond to the cancellation date. In this example, we have $392,409 that were sold on the first quarter of 2020 and cancelled in the third quarter of 2020.

3. Cohort = this is what I need to get done: a percentage table based on the sum of all sales cancelled up until that quarter, divided by the sum of sales concluded up until that quarter.

Currently, my data is structured in PBI like this (random numbers here):

Could anyone help? Thanks!

[Anonymous]

Hi @juliamacg_ ,
Thanks for Fowmy reply.

You can also try the following steps
Open power query and create a table 2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY1LDoAwCAXv0rUJvEfxcwzXjfe/hq0VtRtZDYTJlJJoeUtTMm1TgYIsVDZeKgRjv8E6HFNJoF1KuLP4804hYmG4+XVr9/pTde2tJwsVmIy5wTVXtEt0V8E3jPVP9h6m3/JQa8smo/Taxwk=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CONTRACT = _t, VALUE = _t, #"CONCLUSION DATE" = _t, #"CANCEL DATE" = _t, #"CONCLUSION QUARTER" = _t, #"CANCEL QUARTER" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CONTRACT", Int64.Type}, {"VALUE", Int64.Type}, {"CONCLUSION DATE", type date}, {"CANCEL DATE", type date}, {"CONCLUSION QUARTER", type text}, {"CANCEL QUARTER", type text}}),
    UniqueValues = List.Distinct(Source[CONCLUSION QUARTER]),
    Result = Table.AddColumn(#"Changed Type", "CANCEL_QUARTER", each UniqueValues),
    #"Expanded CANCEL_QUARTER" = Table.ExpandListColumn(Result, "CANCEL_QUARTER"),
    AddCustom = Table.AddColumn(#"Expanded CANCEL_QUARTER", "CancelValue", each if [CANCEL QUARTER] = [CANCEL_QUARTER] then [VALUE] else null),
    #"Changed Type1" = Table.TransformColumnTypes(AddCustom,{{"CancelValue", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 1, 1, Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"VALUE", "CONCLUSION DATE", "CANCEL DATE", "CONCLUSION QUARTER", "CANCEL QUARTER"})
in
    #"Removed Columns"

Create relationships between Table and table2

Create mesures

Conclusionvalue = 
IF(
    SELECTEDVALUE('Table'[CONCLUSION QUARTER]) = SELECTEDVALUE(Table2[CANCEL_QUARTER]),
    MAX('Table'[VALUE]),
    0
)

Running Total = 
VAR _currentIndex = MAX(Table2[Index])
VAR _running = 
CALCULATE(
    SUM(Table2[CancelValue]),
    FILTER(
        ALLEXCEPT(
            Table2,
            Table2[CANCEL_QUARTER]
        ),
        Table2[Index] <= _currentIndex
    )
)
RETURN
IF(
    _running = BLANK(),
    0,
    _running
)

Result = DIVIDE([Running Total],[Conclusionvalue],0)

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

[Fowmy · Accepted solution]

@juliamacg_ 

Please check the following solution it should work for you. I am not sure if you should have a separate column for values that are cancelled in case partial cancellation happens, this will help you calculate the % as well.

Create a table:

SELECTCOLUMNS (
    DISTINCT (
        UNION (
            DISTINCT ( Table01[CONCLUSION QUARTER] ),
            DISTINCT ( Table01[CANCEL QUARTER] )
        )
    ),
    "Quarter", Table01[CONCLUSION QUARTER]
)

Base measure: 

Amount = SUM(Table01[VALUE])

Net Amount:

Net Cumm Amount =
VAR __Concluded =
    CALCULATE (
        [Amount],
        KEEPFILTERS ( Table01[CONCLUSION QUARTER] <= MAX ( 'Column Qtr'[Quarter] ) )
    )
VAR __Cancelled =
    CALCULATE ( [Amount], Table01[CANCEL QUARTER] <= MAX ( 'Column Qtr'[Quarter] ) )
RETURN
    __Concluded - __Cancelled

Result: