SUMX Over Distinct and Max

[gclements]

Hi,

I have a denormalised header and detail fact table.  The header table contains channels per CLI per Billing Period and are non-addiditve across the same CLI as they represent the number of channels at a point in time.  The channels are also duplicated across the ChargeID.  I need to SUM the channels taking Distinct CLI and MAX Billing Period, see the table below.  The channels are currently totaling 180 but they need to total 60.

I should also mention that this table can contain millions of records so ideally I need the most efficient solution.

I feel like I need to create a table expression containing Distinct CLI, Max Billing Period, Max Connections and then SUMX over that but not sure how.

Any help is greatly appreciated.

CLI	BillingPeriod	Charge ID	Channels	Cost	Correct Channels
07700000012	202101	1	10	£5.00
07700000012	202101	2	10	£3.00
07700000012	202102	3	20	£4.00
07700000012	202102	4	20	£3.00	20
07700000013	202101	5	30	£7.00
07700000013	202102	6	20	£4.00
07700000013	202102	7	20	£5.00	20
07700000014	202102	8	10	£5.00
07700000014	202102	9	10	£3.00	10
07700000015	202101	10	10	£3.00
07700000015	202101	11	10	£2.00
07700000015	202101	12	10	£2.00	10
			180	£46.00	60

[gclements · Accepted solution]

Thanks for the replies but unfortunately they were not working for me.
I have managed to solve it using the following measure:

Channels Total = 
SUMX( 
    VALUES ( Charges[CLI] ), 
    CALCULATE (
        CALCULATE (
            MAX ( Charges[Channels] ),
            FILTER (
                VALUES ( Charges[BillingPeriod] ),
                Charges[BillingPeriod] = MAX ( Charges[BillingPeriod] )
            )
        )
    )
)

I have updated the sample project to reflect this:

https://1drv.ms/u/s!AoTVsSI4n62qjZ0Mq4u-5-4lAzqDqw?e=mYde5a

If anyone know a more efficient way to right this then I would love to hear.

Also I'm not sure why I had to use CALCULATE twice but it was the only way I could include part of the code without having to create a seperate measure. 

If anyone has a good explanation for this then please let me know.

[ryan_mayu]

@gclements 

maybe you can try

Measure = sumx(VALUES(Charges[CLI]),[SumMaxBP])

[Tahreem24]

@gclements ,

Try this measure:

Measure =

VAR Answer_ = CALCULATE(MAX('Table'[Charge ID]),ALLEXCEPT('Table','Table'[CLI]))

RETURN CALCULATE(SUM('Table'[Channels]),FILTER('Table','Table'[Charge ID]=Answer_))

[gclements]

Thanks for the reponse.  Unfortunately this isn't working for me either as it is not filtering by MAX(BillingPeriod), the MAX(ChargeID) may not necessarily be the latest state of the CLI as data is not always entered chronologically.

I have had a go at this myself and have got so far but cant seem to finalise it.

Please view my power bi project here:

https://1drv.ms/u/s!AoTVsSI4n62qjZ0Mq4u-5-4lAzqDqw?e=mYde5a

[amitchandak]

@gclements , Create a measure like

sumx(summarize(Table, Table[CLI], Table[Channels]), [Channels])

or

sumx(values(Table[CLI]), calculate(Max (Table[Channels])))

[gclements]

This doesnt filter on the MAX(Billing Period) so will give the wrong result, such as 07700000013 the most recent channels is 20, not 30 but MAX(Channels) will result in 30.  It needs to be the channels associated with the MAX(Billing Period).