how to display variation in Matrix

[cedboumfrancois]

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

[ThomasDaSilva]

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.

[v-haibl-msft]

@cedboumfrancois

Please try to uncheck “Show items with no data” as below.

Best Regards,

Herbert

[cedboumfrancois]

all the fields are already uncheck.

😞

[v-haibl-msft]

@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

[cedboumfrancois]

my goal is to display VE en K (2015) / VE en K (2016) / VAR N-1 (2016)

if i remove VE en K i will finish whith one single column, not what i want.

I'm surprised that such a basic table can not be done. I can't be the only one to need this kind of matrix.

it seems to be more tricky than expected.

I think Power BI is just great for graphs but very poor for table.

I hope some improvement in the near future for it, because for the moment it is quite ashamed. 

[v-haibl-msft]

@cedboumfrancois

You can create an idea in http://ideas.powerbi.com for this feature to improve the Matrix visual.

Best Regards,

Herbert

[cedboumfrancois]

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]

@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]

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?

[tjd]

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.