Substract measure based on the two columns from the same table

[Anonymous]

Hi,

I have a table that looks like this

a measure Offer movements which is a sum of kpi_frml_mvt and two referential tables that go to id_formule_ini (Package ini) and id_formule_fin (Package fin).

When I put the data into the matrixes it looks like this:

What I need is to be able to substract this data. So in the first line for ACCESS it would be 17-40=-23, for DECOUVERTE it would be 79-2=77 etc.

Does anyone have an idea on how it can be done? I'm stuck at this point...

[Syndicate_Admin]

If you have it all in a single table, then easier: I would create a calculated column that gives you the name Package ini and Package fin called Package.

You create a dimension with the Package column with unique values and do the same thing, measure that adds all the Package ini, another that adds Package end, and another that subtracts Package ini and Package end. Then in the Report, you can put the table with the Package dimension and the calculated measure.

[Syndicate_Admin]

Hi Slowik131,

What it would do would be the following:

• Create a dimension table (DimPackage) that contains all the Package ini and Package end without duplicates.
  

   

• Add an index column called CodPackage
• 
  

• Merge both Table 1 and Table 2 containing CodPackage from the DimPackage table. And remove the Package ini and Package end column from each table. You should be left with a table with Offer movements and CodPackage

• 
• Review Relationships
• 

• Create the following measures:

  • ALL Offers Table 1 = SUM(Table1[Offer moviments])
  • ALL Offer Table 2 = SUM(Table2[Offer Movements])
  • Total Offers Result = [ALL Offers Table 1]-[ALL Offer Table 2]

    With this, you will have it
    
    
    

    

[Anonymous]

Hi, it seems that we now have two tables: Table1 and Table2 which have a column for offer movements. In my case I need to have only one table. My Offer movements table containts around 50m rows, hence I can't duplicate it.

So I have to work on a table that contains id_package_ini and id_package_fin at the same time

[Greg_Deckler · Accepted solution]

@Anonymous So, not sure I completely understand but it seems like you need a disconnected table that contains a column with ACCESS, DECOUVERTE, etc. Then you could use this table in your matrix and perform the necessary calculations. Since you could use the value of the column in context to essentially go perform the calculation on a filtered set of your dimension columns.

Not really much else I can say without being able to mock it up and I'm not sure I understand your semantic model fully.

[Anonymous]

Hi, thanks for the reply! 

So to follow it up, the mesaure for the disconnected table should look something like this for one of the parts, right?

Measure = var _sel = SELECTEDVALUE('Packages disconnected'[ID Package])

return

CALCULATE([Offer Movements], _sel = 'Offer Movements'[id_formule_ini])

and then just change ini to fin