Forum Discussion
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. ...
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:
Fowmy
Super User
Super Userjuliamacg_
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: