compare 2 versions of dataset: difference between 2 measures based on seperate 2 filters

[Anonymous]

Hello,

I want to show the difference between 2 versions of a dataset. I created 2 measures and 2 filters for this. 1 filter has interaction with only 1 measures. This works fine, but the delta between these 2 measures is not working. Also not when I change the interactions with the filters. 

I tried for the delta measure 2 options:

1. Measure 1 - Measure 2

2. Full 2 measures in one measure subtracting one of the other: 

 CALCULATE(SUM(Source[Amount]);

FILTER(Source;Source[Versions]

= SELECTEDVALUE('Main Version'[Version])))

- 

CALCULATE(SUM(Source[Amount]);

FILTER(Source;Source[Versions]

= SELECTEDVALUE('Second Version'[Version])))

Measure 1: CALCULATE(SUM(Source[Amount]);

FILTER(Source;Source[Versions]

= SELECTEDVALUE('Main Version'[Version])))

Measure 2: 

CALCULATE(SUM(Source[Amount]);

FILTER(Source;Source[Versions]

= SELECTEDVALUE('Second Version'[Version])))

For the filters I created 2 reference tables: 

1. Main Version with a column 'Version' with all versions in them. This table is linked to Table 'Source'. 

Filter 'Main Version' contains this column Version from table Main Version

2. Second Version with a column 'Version' with all versions in them. This table is linked to Table 'Source'. 

Filter 'Second Version' contains this column Version from table Second Version

Any ideas how to solve showing the delta?

[v-yuta-msft · Accepted solution]

@Anonymous,

Please change the measure with formula below and try again:

MainVersion =
CALCULATE (
    SUM ( 'Dataleversie'[Waarde] );
    FILTER (
        ALLEXCEPT ( 'Dataleversie'; 'Main Version' );
        Dateleversie[Version] = 'Main Version'[Mainversionselected]
    )
)

Regards,

Jimmy Tao

[v-yuta-msft]

Hi MartijnNL,

"1. Main Version with a column 'Version' with all versions in them. This table is linked to Table 'Source'. 

Filter 'Main Version' contains this column Version from table Main Version

2. Second Version with a column 'Version' with all versions in them. This table is linked to Table 'Source'. 

Filter 'Second Version' contains this column Version from table Second Version"

<--- Could you please share some sample data and more details about your requirement? (e.g.: Delta measure, Main version and Second measure)

Regards,

Jimmy Tao

[Anonymous]

Source table: 

Version	Amount
LE 08	100
LE 10	90

Table 'Main Version' with  version connected to Source:

Version

LE 01

LE 02

LE 03

LE 04

LE 05

LE 06

LE 07

LE 08

LE 09

LE 10

LE 11

LE 12

Table 'Second Version  with version connected to Source:

Version

LE 01

LE 02

LE 03

LE 04

LE 05

LE 06

LE 07

LE 08

LE 09

LE 10

LE 11

LE 12

2 filters using on of these main version and second version. I use 2 measures to save the selectedvalue of the filter:

Mainversionselected = SELECTEDVALUE('Main Version'[Version])

Secondversionselected = SELECTEDVALUE('Second Version'[Version])

and then use these in 2 measures:

Main Version = CALCULATE (SUM('Source'[Amount]);filter ('Source';('Source'[Version] = 'Main Version'[Mainversionselected]))

 Second Version: = CALCULATE (SUM('Source'[Amount]);filter ('Source';('Source'[Version] = 'Second Version'[Secondversionselected]))

Then I create 2 cards with each one contain one of the measures. And that only 1 filter has connection to this card.

This works.

Only the delta is not working in a visual. Disconnecting both filters from this card with the delta in it. or connecting both, seems to have no good result. Result should be 10.

Tried these 2 measures for the delta:

1. Delta = [Main Version] - [Second Version]

2. Delta = CALCULATE (SUM('Source'[Amount]);filter ('Source';('Source'[Version] = 'Main Version'[Mainversionselected])) - CALCULATE (SUM('Source'[Amount]);filter ('Source';('Source'[Version] = 'Second Version'[Secondversionselected]))

[Anonymous]

Anyone an idea how to solve this?

[v-yuta-msft]

@Anonymous,

Please refer to steps below:

Click either of the slicer based on 'Main Version' and 'Second Version', then click Format-> Edit interaction, disable the filter from both side so that you can select different values in different slicers.

 

Change the direction from both to single between fact table and dimension table:

 

Finally modify the measures in 'Main Version' like below:

Main Version = CALCULATE (SUM('Source'[Amount]), filter (ALLEXCEPT('Source', 'Main Version'), 'Source'[Version] = 'Main Version'[Mainversionselected]))

Second Version = CALCULATE (SUM('Source'[Amount]), filter (ALLEXCEPT('Source', 'Second Version'), 'Source'[Version] = 'Second Version'[Secondversionselected]))

  

Regards,

Jimmy Tao

[Anonymous]

Hi @v-yuta-msft thank you! I will try this today and reply when it is solved!

[Anonymous]

Hi @v-yuta-msft  I get this issue (I use some different names for the columns but data is the same as metioned before)

[v-yuta-msft]

Hi MartijnNL,

Could you please share some screenshots of your slicer?

Regards,

Jimmy Tao

[Anonymous]

[v-yuta-msft · Accepted solution]

@Anonymous,

Please change the measure with formula below and try again:

MainVersion =
CALCULATE (
    SUM ( 'Dataleversie'[Waarde] );
    FILTER (
        ALLEXCEPT ( 'Dataleversie'; 'Main Version' );
        Dateleversie[Version] = 'Main Version'[Mainversionselected]
    )
)

Regards,

Jimmy Tao

[Raul_Vinuesa]

Same issue for me. But, does this solution works whether I show two versions in a table? Not sure that it works with this data model.

[Anonymous]

@v-yuta-msft thanks after some tweaking it works.

[Anonymous]

@v-yuta-msft  one extra question. I want to use other filters in the top of the dashboard, e.g. to select a KPI or a Year or Month.  When I put KPi name in a filter, nothing happens. Is this due to the  'Filter (AllExcept'  function? 

Any idea how to solve?

I tried this with the following measures:

MainVersionwithFilters = SUMX(FILTER(Source; Source[Versie] = MainVersion[MainVersionSelected]);Source[Waarde])

SecondVersionwithFilters = SUMX(FILTER(Source; Source[Versie] = SecondVersion[SecondVersionSelected]);Source[Waarde])

Delta = [MainVersionwithFilters] - [SecondVersionwithFilters]

Then the Delta is not working correctly:

Pbix link

[Anonymous]

@v-yuta-msft thanks the measure is ok now but still not working properly. Not sure what I am doing wrong:

Measure MainVersion:

MainVersion = CALCULATE (SUM (Data1eversie[Waarde] );

FILTER ( ALLEXCEPT ( Data1eversie; 'Main Version' );

Data1eversie[Version] = 'Main Version'[Mainversionselected]

)

)

Also, I created this one that does work:

KPI versie 1 = CALCULATE(SUM(Data1eversie[Waarde]))

But with the last one, the delta cannot be measures so this will not work either.

[v-yuta-msft · Accepted solution]

@Anonymous,

Could you share a sample pbix for further analysis?

Regards,

Jimmy Tao