Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am having an issue of empty cells in my cohorted matrix table where values do not change from the previous cohorted month to the next.
I would like to have these empty cells show the value in the previous cells to indicate no increment at the given cohort month.
Cohort revenue is a cumulative measure, so the values increase with each subsequent cohort month. When a subsequent month has no increment in cohorted revenue, I would like the value of the previous cohorted month to be repeated until there is a newer cohorted month with an incremented value.
Currently, I am getting:
The desired result:
Here is how I calculate the Cohorted revenue measure:
Cohorted revenue = CALCULATE([Revenue],
FILTER(
ALLSELECTED('Purchases'[Cohort month]),
ISONORAFTER('Purchases'[Cohort month], MAX('Purchases'[Cohort month]),DESC)
)
)
I would appreciate it if anyone could explain to me how I can achieve my desired result.
Thanks. 😊
Solved! Go to Solution.
@Sul , try the following:
I just typed the following without testing since I don't quite know the data you are using and cannot test if they are correct.
Cohorted revenue =
VAR SelectedMonth = MAX('Purchases'[Cohort month])
VAR MaxMonth = CALCULATE(MAXX(ALLSELECTED(Purchases'[Cohort month]),Purchases'[Cohort month]),REMOVEFILTERS(Purchases'[Cohort month]))
VAR Result = CALCULATE([Revenue],FILTER(ALLSELECTED('Purchases'[Cohort month]),'Purchases'[Cohort month] <= SelectedMonth))
RETURN
IF(MAX('Purchases'[Cohort month])>MaxMonth,BLANK(),Result)
@Sul , try the following:
I just typed the following without testing since I don't quite know the data you are using and cannot test if they are correct.
Cohorted revenue =
VAR SelectedMonth = MAX('Purchases'[Cohort month])
VAR MaxMonth = CALCULATE(MAXX(ALLSELECTED(Purchases'[Cohort month]),Purchases'[Cohort month]),REMOVEFILTERS(Purchases'[Cohort month]))
VAR Result = CALCULATE([Revenue],FILTER(ALLSELECTED('Purchases'[Cohort month]),'Purchases'[Cohort month] <= SelectedMonth))
RETURN
IF(MAX('Purchases'[Cohort month])>MaxMonth,BLANK(),Result)
Hello @johnyip ,
Thanks for your help. Your formula seems to work, but it creates another issue. 😅
There empty cells are now correctly filled with the previous max cohorted revenue value, however, it last max value run all the way to the last cohort moth for all rows, including rows that contain values that begun in the first cohorted month.
What I am getting now:
My desired result:
I can't seem to figur out why the duplicated values up uptil the last cohort month for the months Jul to Dec.
Do you have an idea why this occurs?
I think this should be because the data you are using includes every "cohorted month" for each "month-year", even when there is no data.
Or, some other reason (i.e. [Revenue], or the data you used)
Cannot quite tell if I cannot look into the PowerBI file with similar data structure of your data you are using.
Hi @Sul
Try this and see if it can help you
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Thanks @aj1973 for the solution you suggested but this doesn't solve the issue.
I'm am getting incorrect values populating the entire table.
It does not seem to work for this problem.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |