cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Betissa Regular Visitor
Regular Visitor

Prob in difference between 2 columns Help me

Hello I would like to use a matrix in the visualization part which displays a field representing the difference between two columns displayed by the same matrix how can I do to achieve this manipulation ?
Your suggestions !!
thank you in advance.ecart.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Framet Regular Visitor
Regular Visitor

Re: Prob in difference between 2 columns Help me

Hi,

 

Just quickly I'd recommend that where possible you have dates instead of things like PreviousYear, Currentyear etc however given what we have:

 

When you say make a grouping what sort of grouping do you want? If we assume SUM then I would write a measure for that first.

Sum Mt HT :=
SUM ( YourTable[MtHT] )

 

 

If used in Power BI this will immediately give you the sum of Mt HT by customer regardless of how many repeats you have.

To deal with Rubrique2, I imagine you want side by side comparisons which, provided only a hand full of values exists in Rubrique2, could be dealt with using a few more measures:

Sum Mt HT CurrentYear :=
CALCULATE ( SUM ( YourTable[Mt HT] ), YourTable[Rubrique2] = "Currentyear" )
Sum Mt Ht PreviousYear :=
CALCULATE ( SUM ( YourTable[Mt HT] ), YourTable[Rubrique2] = "Previousyear" )
Sum Mt HT Budget N :=
CALCULATE ( SUM ( YourTable[Mt HT] ), YourTable[Rubrique2] = "Budget N" )

 

 

And from this you can create a comparison of current to previous year with a simple subtraction:

 

Current Year Variance to Previous Year:=[Sum Mt Ht CurrentYear] - [Sum Mt HT PreviousYear]

These measures should return the correct results if filtered by customer or aggregated.

 

I apologise if the answer is too basic for your needs, I'm trying to guage where you are at in terms of understanding.

 

Regards

 

Thomas

 

3 REPLIES 3
Framet Regular Visitor
Regular Visitor

Re: Prob in difference between 2 columns Help me

Hi There,

 

It is a little tricky to help from that redacted image as guidance but I'll give it my best shot. 

Assuming you always want the difference between what ever [Mt HT] is for a given row and the value of [Mt HT] when Rubrique2 = "Realise + Liv N" then you could try:

[Ecarttt] :=
[Mt HT]
    - CALCULATE ( [Mt HT], Rubrique2 = "Realise + Liv N" )

This should if used in place of whatever you have for [Ecarttt] at the moment return 0.00 in the first column and the difference in the second. I apologise as I mention it is hard to work from the image and I haven't typed your accents in the names.

Hope this helps at least a little.

 

Thanks

 

Thomas

Betissa Regular Visitor
Regular Visitor

Re: Prob in difference between 2 columns Help me

Hi Thomas , thanks for your answer.

Here is my dataset I would like to make a grouping of the Mt HT according to the field Field and to make the difference between the Mt Ht of Currentyear and and the Mt Ht of PreviousYear for each customer in order to see the evolution of the MtHt according to the two successive years.

extract.PNG
It can be noted that a single customer can repeat 2 or 3 times in the same year
I am very new in the world of power bi, what you propose me to do.
Thank you very much.

 

 

Framet Regular Visitor
Regular Visitor

Re: Prob in difference between 2 columns Help me

Hi,

 

Just quickly I'd recommend that where possible you have dates instead of things like PreviousYear, Currentyear etc however given what we have:

 

When you say make a grouping what sort of grouping do you want? If we assume SUM then I would write a measure for that first.

Sum Mt HT :=
SUM ( YourTable[MtHT] )

 

 

If used in Power BI this will immediately give you the sum of Mt HT by customer regardless of how many repeats you have.

To deal with Rubrique2, I imagine you want side by side comparisons which, provided only a hand full of values exists in Rubrique2, could be dealt with using a few more measures:

Sum Mt HT CurrentYear :=
CALCULATE ( SUM ( YourTable[Mt HT] ), YourTable[Rubrique2] = "Currentyear" )
Sum Mt Ht PreviousYear :=
CALCULATE ( SUM ( YourTable[Mt HT] ), YourTable[Rubrique2] = "Previousyear" )
Sum Mt HT Budget N :=
CALCULATE ( SUM ( YourTable[Mt HT] ), YourTable[Rubrique2] = "Budget N" )

 

 

And from this you can create a comparison of current to previous year with a simple subtraction:

 

Current Year Variance to Previous Year:=[Sum Mt Ht CurrentYear] - [Sum Mt HT PreviousYear]

These measures should return the correct results if filtered by customer or aggregated.

 

I apologise if the answer is too basic for your needs, I'm trying to guage where you are at in terms of understanding.

 

Regards

 

Thomas