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.

amitchandak's avatar
amitchandak
Super User
5 years ago

gclements , Create a measure like

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

 

or

 

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

gclements's avatar
gclements
Helper II
5 years ago

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).