Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Get the MAX SUM except for this data

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 ?

Need Help

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

     

    Try this modification

     

    Inscription =
IF (
    HASONEFILTER ( 'Canaux Online'[Canaux Online] ),
    [InscriptionMeasure],
    SUMX (
        EXCEPT ( VALUES ( 'Canaux Online'[Canaux Online] ), { "SMO" } ),
        [InscriptionMeasure]
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-lili6-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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