Get the MAX SUM except for this data

[Anonymous]

Previously I had an issue that @Zubair_Muhammad solved here : https://community.powerbi.com/t5/Desktop/Matrix-Total-in-a-measure-doesn-t-sum-up/m-p/729524#M352008

I needed to get the max sum value from differents sources but having the total sum not as the MAX(SUM()) but the SUM of the returned data.

For that I added a first measure

InscriptionMeasure = 
MAX (
    SUM ( 'Prod Asso Inscrites OnLine'[Inscription Prod] ),
    SUM ( 'GA Asso Inscrites OnLine'[Associations Inscrites] )
)

Then from that I added a second one that I used in my matrix:

Inscription = 
IF (
        HASONEFILTER ( 'Canaux Online'[Canaux Online] ),
        [InscriptionMeasure],
        SUMX ( VALUES ( 'Canaux Online'[Canaux Online] ), [InscriptionMeasure] )
)

Now I need to had another IF inside this logic.

I want the exacte same result except if the column is "Canaux Online" = "SMO" I need to had the SUM coming from Another table.

I try differente solution but I cannot make this work. Any idea how ?

[Zubair_Muhammad]

@Anonymous 

Try this modification

Inscription =
IF (
    HASONEFILTER ( 'Canaux Online'[Canaux Online] ),
    [InscriptionMeasure],
    SUMX (
        EXCEPT ( VALUES ( 'Canaux Online'[Canaux Online] ), { "SMO" } ),
        [InscriptionMeasure]
    )
)

[Anonymous]

Thank you @Zubair_Muhammad 

It doesn't seem to do anything.

Also even if I can exclude the SMO part, how can I add in place the SUM from the other source ?

[v-lili6-msft]

HI, @Anonymous 

It should work well, if you want conditional from other source, you could try this way

Inscription 33 = 
IF (
    HASONEFILTER ( 'Canaux Online'[Canaux Online] ),
    [InscriptionMeasure],
    SUMX (
        EXCEPT ( VALUES ( 'Canaux Online'[Canaux Online] ), VALUES('other source'[Canaux Online]) ),
        [InscriptionMeasure]
    )
)

if possible, please share a simple sample pbix file for us have a test.

You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin