Forum Discussion

juliamacg_'s avatar
juliamacg_
Frequent Visitor
1 year ago
Solved

Help building a cohort matrix percentage visualization

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!

Need Help
  • Fowmy's avatar
    Fowmy
    1 year ago

    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:

     



2 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    1 year ago

    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:

     



  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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