Calculate Max value of a running total

[Pepperi]

Hi,

I'm experimenting with PowerBI, which is a fantastic tool and have a great community.

I have a table of deals per customer. We are selling our products as license per user, therefore a customer might add or reduce the number of licenses during its global subscription.

I've created a cumulative total measure and i want now to be able to determine for each customer what was its highest subscription since it's a customer.

For instance, all the deals in the large red rectangle relate to one customer. The highest value is in the small red rectangle.

I'm stuck because the running total is a measure, and the MAX function requires a column.

Can you help me on this one?

Thanks in advance

[v-yulgu-msft]

Hi @Pepperi,

Please refer to below measures:

Rank =
RANKX (
    ALLEXCEPT ( 'Test Table', 'Test Table'[Custom] ),
    [OPP MRR USD running total],
    ,
    DESC,
    DENSE
)

Max running total =
CALCULATE (
    [OPP MRR USD running total],
    FILTER ( ALLEXCEPT ( 'Test Table', 'Test Table'[Custom] ), [Rank] = 1 )
)

Best regards,

Yuliana Gu

[Pepperi]

@v-yulgu-msft

Hi Yuliana, thank you very much for your reply.

In your example, assuming the deals are sorted by date, I don't understand the cumulative total.
The figures should be:

Anyway, i tried your solution but the results are not as expected:

[v-yulgu-msft]

Hi @Pepperi,

In your scenario, how did you calculate the Running Total values? Also, are records sorted by date in your scenario?

Regards,

Yuliana Gu

[Pepperi]

Hi @v-yulgu-msft,

Sorry for the late reply.

My issue was solved. Thanks for your help!

[joemuziki]

Hi @Pepperi how did you end up solving this?

[lax-stevens]

Can you share the DAX for this problem? I'm running into the same issue