Forum Discussion

cedboumfrancois's avatar
cedboumfrancois
Icon for Advocate I rankAdvocate I
9 years ago

how to display variation in Matrix

Hello all,

 

 

I want to display YtoY variation in a Matrix but could not find the way to do it with duplicating columns

 

Example :

 For 2016 i want VAR N-1 and VAR -1 K appearing but not for 2015 since i d'ont have have any 2014 values in my database.

below the formula for VAR N-1

 

VAR N-1 = calculate (sum([VE]); filter ([Table]; [Table].[Year]="2016"))- sum([VE]); filter ([Table]; [Table].[Year]="2015")))

 

Could you please help?

 

Thanks

 

 

Data Modeling

10 Replies

  • tjd's avatar
    tjd
    Icon for Impactful Individual rankImpactful Individual
    9 years ago

    Try wrapping your measure(s) in an IF statement that checks the previous year's values and returns a null for the measure if the previous year's value is null.

  • cedboumfrancois's avatar
    cedboumfrancois
    Icon for Advocate I rankAdvocate I
    9 years ago

    Thanks  tjd for the tip.

     

    I've tried several if formulas but i'm a newbie in DAX.

     

    Var N-1 = if(

     

          filter('VE journalier_mois';'VE journalier_mois'[Produit]="2015");

     

          null;

     

         (CALCULATE(sum('VE journalier_mois'[VE]);FILTER('VE journalier_mois';'VE journalier_mois'[Année Fact.]=2016))-CALCULATE(sum('VE journalier_mois'[VE]);FILTER('VE journalier_mois';'VE journalier_mois'[Année Fact.]=2015)))

    )

     

    This do not work.

    Any clue?

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    cedboumfrancois

     

    You should post in other forums but not Issues for need help topic. You can try with following measure formula.

     

    Var N-1 =
IF (
    CALCULATE (
        LASTNONBLANK (
            'VE journalier_mois'[Année Fact.];
            'VE journalier_mois'[Année Fact.]
        )
    )
        = "2015";
    BLANK ();
    CALCULATE (
        SUM ( 'VE journalier_mois'[VE] );
        FILTER ( 'VE journalier_mois'; 'VE journalier_mois'[Année Fact.] = 2016 )
    )
        - CALCULATE (
            SUM ( 'VE journalier_mois'[VE] );
            FILTER ( 'VE journalier_mois'; 'VE journalier_mois'[Année Fact.] = 2015 )
        )
)

     

    Best Regards,

    Herbert

  • cedboumfrancois's avatar
    cedboumfrancois
    Icon for Advocate I rankAdvocate I
    9 years ago

     

    Thanks a lot.

     

    Sorry next time i will post in the correct forum

     

    I used your formula. It works, but in terms of display the empty is this remaining. Is there any to show only the 3 filled columns?

     

      • v-haibl-msft's avatar
        v-haibl-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        cedboumfrancois

         

        I see... VE en K has value in 2015. So even if you uncheck Show items with no data for the Year column, Var N-1 V2 will still display in the Matrix.

        If we remove VE en K from Values field, the blank Var N-1 V2 will not show in the Matrix. It should be as expected.

         

        Best Regards,

        Herbert

  • ThomasDaSilva's avatar
    ThomasDaSilva
    Icon for Advocate II rankAdvocate II
    8 years ago

    Bonjour, 

     

    Je viens de tomber par hasard sur ton sujet et je serai très intéréssé de savoir comment tu as fait des titres regroupant plusieurs colonnes (2015,2016). J'aimerais faire la même chose mais je n'y parviens pas.

     

    Merci d'avance.