cancel
Showing results for
Did you mean:
Helper I

## Cohort - Calculation Troubles - Calculate / Divide and MIN ?

Hello All,

I'm using the Matrix visual in Power BI to create a "Cohort" analysis.

As you can see below, we have the date month of the first order (first column) and then by month how many customers stayed.

The table is build in a was that we have no date intelligence. Thus I'm solely relying on min/max (but it doesn't work).

In the expected result table, you can see that I want to :
Divide(
Calculate(sum(value)),
Calculate(sum(value)),
Filtered by ? To obtain the minimum of the row.

Exemple: 80/150=53% for 201704 on the row 201701

the "201701" are number and represent the year & month.

Best,

BT

1 ACCEPTED SOLUTION
Community Support

Hi @ING_BT ,

Please refer to my pbix file to see if it helps you.

Craete a measure.

``````Measure =
VAR _mindate =
CALCULATE (
MIN ( 'Table'[date 2] ),
FILTER (
ALL ( 'Table' ),
'Table'[date] = SELECTEDVALUE ( 'Table'[date] )
&& 'Table'[value] <> BLANK ()
)
)
VAR mu_ =
CALCULATE (
MAX ( 'Table'[value] ),
FILTER (
ALL ( 'Table' ),
'Table'[date] = SELECTEDVALUE ( 'Table'[date] )
&& 'Table'[date 2] = _mindate
)
)
VAR zi =
MAX ( 'Table'[value] )
RETURN
DIVIDE ( zi, mu_ )
``````

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Helper I

Hello Polly,

Thank you for your solution, it helps on the information I've provided !
Unfortunatly, my data set is quite differente with several dimensions&filters; so I'm working on modifiying this first and adapte your approach.

Best,

BT

Community Support

Hi @ING_BT ,

Please refer to my pbix file to see if it helps you.

Craete a measure.

``````Measure =
VAR _mindate =
CALCULATE (
MIN ( 'Table'[date 2] ),
FILTER (
ALL ( 'Table' ),
'Table'[date] = SELECTEDVALUE ( 'Table'[date] )
&& 'Table'[value] <> BLANK ()
)
)
VAR mu_ =
CALCULATE (
MAX ( 'Table'[value] ),
FILTER (
ALL ( 'Table' ),
'Table'[date] = SELECTEDVALUE ( 'Table'[date] )
&& 'Table'[date 2] = _mindate
)
)
VAR zi =
MAX ( 'Table'[value] )
RETURN
DIVIDE ( zi, mu_ )
``````

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements