Forum Discussion

gclements's avatar
gclements
Helper II
5 years ago
Solved

SUMX Over Distinct and Max

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 ch...
Need Help
  • gclements's avatar
    gclements
    5 years ago

    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.

Tahreem24's avatar
Tahreem24
Super User
5 years ago

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's avatar
gclements
Helper II
5 years ago

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