Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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]);
CALCULATE(SUM(Source[Amount]);
Measure 1: CALCULATE(SUM(Source[Amount]);
Measure 2:
CALCULATE(SUM(Source[Amount]);
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?
Solved! Go to 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
@Anonymous,
Could you share a sample pbix for further analysis?
Regards,
Jimmy Tao
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
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:
Anyone an idea how to solve this?
@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
Hi @v-yuta-msft I get this issue (I use some different names for the columns but data is the same as metioned before)
Hi MartijnNL,
Could you please share some screenshots of your slicer?
Regards,
Jimmy Tao
@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
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.
@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:
@v-yuta-msft thanks the measure is ok now but still not working properly. Not sure what I am doing wrong:
Measure MainVersion:
Also, I created this one that does work:
@Anonymous,
Could you share a sample pbix for further analysis?
Regards,
Jimmy Tao
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
64 | |
63 |
User | Count |
---|---|
142 | |
105 | |
102 | |
80 | |
67 |