Difference column in a matrix

[Anonymous]

Hi,

i'd calculate the difference in a matrix between values from the same column [HRS_PLAN_DELTA], for each IDS in the same date, for different ID_Report.

Do you have some ideas ? 

Dom

[Anonymous]

This is quite similar in principle to this solution: https://community.powerbi.com/t5/Desktop/Difference-between-rows-with-different-categories/m-p/65702... - you could try using this type of solution to search for the row values to compare. 

However, you will have to be more specific on what you are trying to achieve to get a solution that works. 

Can you show your expected result and the working for one example? 

[Anonymous]

Hi,
i'd a result like this:

Dom

[Anonymous]

If you can paste you data in that would be great

[Anonymous]

@Anonymous 

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

To pivot the table and add a custom column in power query can get the excepted result we need. M code for your refernce.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc7LCUAhDETRXrIWdEafn1rE/ttQwgskCyFwSK57CwhJoi8js2C98ZOTjKhEo+6oKlWj6agpNSPgN+r1EAOd9VjDcDZizq/NmHs/ORc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, IDS = _t, Date = _t, HRS_PLAN_DELTA = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"IDS", Int64.Type}, {"Date", type date}, {"HRS_PLAN_DELTA", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"IDS"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"ID", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"ID", type text}}, "en-US")[ID]), "ID", "HRS_PLAN_DELTA", List.Sum),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Custom", each [121]-[122])
in
    #"Added Custom"

Regards,

Frank

[Anonymous]

I've created this measure: 

DIFFERENCE = 
var min_id_report = MIN(COPR_RECAP[REVISION])
var max_id_report = MAX(COPR_RECAP[REVISION])
return CALCULATE(SUM(COPR_DETAIL[HRS_PLAN_DELTA]); COPR_RECAP[REVISION]=min_id_report) - CALCULATE(SUM(COPR_DETAIL[HRS_PLAN_DELTA]);COPR_RECAP[REVISION]=max_id_report)

The result is ok for 2 [Revisions]
I'd create a difference between a fixed [Revision] and the other ones (3 or 4 revisions).
Do you have some ideas ? 

[Anonymous]

You could do the following:

1. Create a new Calculated Table:

Distinct Revisions = distinct(COPR_RECAP[REVISION])

2. Create a new Measure:

vs Selected Revision = 
SUM(COPR_DETAIL[HRS_PLAN_DELTA])
-
IF(
  HASONEVALUE('Distinct Revisions'[REVISION]) = FALSE(), 0,
  CALCULATE(
    SUM(COPR_DETAIL[HRS_PLAN_DELTA]),
    COPR_DETAIL[REVISION] = VALUES('Distinct Revisions'[REVISION])
  )
)

3. Add a slicer visual with 'Distinct Revisions'[REVISION]

     and Add [vs Selected Revision] to your visual.

4. Or, Add both 'Distinct Revisions'[REVISION] and [vs Selected Revision] to your visual.

5. You made to tweak, add information as necessary, but this pattern should generally work for comparing against a certain value.

Hope this helps,

Nathan