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

     



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