Difference between two identical tables with multiple filters

Anonymous — Mon, 21 Mar 2022 10:59:57 GMT

Hi Team,
Please find the below screenshot, My requirement is to show the difference between two identical tables. I am briefing below.
I have

I have used few tables in this report page.
1. Date Table- YEAR and MONTH are columns, which are used in slicer
2. Store Table- Retail Unit and Selling Unit are columns,which are used in slicer
3. HFB_V Table - HFB Name is the column,which are used in slicer
4. Product Table - PA Name is the column,which are used in slicer

And in the above screenshot, In the table I have used "Gross profit description" column from "Gross profit component Table",

GP and GM % are measures. And those were written as follows:

GP =
IF (
ISFILTERED ( 'Product Range'[PA] ) || ISFILTERED ( 'Product Range'[PRA] ),
IF (
SELECTEDVALUE ( Currency_Filter[Currency] ) = "EUR",
SUM ( GP_LVL_FCT_SDM_PBI_V[Value EUR] ),
IF (
HASONEVALUE ( Store[Retail Unit Name] )
|| HASONEVALUE ( 'Primary Market Area'[Retail Unit Name] ),
SUM ( GP_LVL_FCT_SDM_PBI_V[Value] ),
BLANK ()
)
),
IF (
SELECTEDVALUE ( Currency_Filter[Currency] ) = "EUR",
SUM ( GP_LVL_HFB_AFT_SDM_PBI_V[Value EUR] ),
IF (
HASONEVALUE ( Store[Retail Unit Name] )
|| HASONEVALUE ( 'Primary Market Area'[Retail Unit Name] ),
SUM ( GP_LVL_HFB_AFT_SDM_PBI_V[Value] ),
BLANK ()
)
)
)

GM% =

VAR _a_PA =
(
CALCULATE (
SUM ( GP_LVL_FCT_SDM_PBI_V[Value] ),
ALL ( 'Gross Profit Component' ),
'Gross Profit Component'[Gross Profit Id] = 1
)
)
VAR _b_PA =
(
CALCULATE (
SUM ( 'GP_LVL_FCT_SDM_PBI_V'[Value] ),
ALL ( 'Gross Profit Component' ),
'Gross Profit Component'[Gross Profit Id] = 2
)
)
VAR _d_PA =
DIVIDE (
SUM ( 'GP_LVL_FCT_SDM_PBI_V'[Value] ),
IF (
SELECTEDVALUE ( 'Gross Profit Component'[Gross Profit Id] ) IN { 2, 8, 9, 10 },
_b_PA,
_a_PA
)
) -------EURO-------
VAR _a_EUR_PA =
(
CALCULATE (
SUM ( GP_LVL_FCT_SDM_PBI_V[Value EUR] ),
ALL ( 'Gross Profit Component' ),
'Gross Profit Component'[Gross Profit Id] = 1
)
)
VAR _b_EUR_PA =
(
CALCULATE (
SUM ( 'GP_LVL_FCT_SDM_PBI_V'[Value EUR] ),
ALL ( 'Gross Profit Component' ),
'Gross Profit Component'[Gross Profit Id] = 2
)
)
VAR _d_EUR_PA =
DIVIDE (
SUM ( 'GP_LVL_FCT_SDM_PBI_V'[Value EUR] ),
IF (
SELECTEDVALUE ( 'Gross Profit Component'[Gross Profit Id] ) IN { 2, 8, 9, 10 },
_b_EUR_PA,
_a_EUR_PA
)
) --------------------PA------------
--------LOC--------
VAR a =
(
CALCULATE (
SUM ( GP_LVL_HFB_AFT_SDM_PBI_V[Value] ),
ALL ( 'Gross Profit Component' ),
'Gross Profit Component'[Gross Profit Id] = 1
)
)
VAR b =
(
CALCULATE (
SUM ( 'GP_LVL_HFB_AFT_SDM_PBI_V'[Value] ),
ALL ( 'Gross Profit Component' ),
'Gross Profit Component'[Gross Profit Id] = 2
)
)
VAR d =
DIVIDE (
SUM ( 'GP_LVL_HFB_AFT_SDM_PBI_V'[Value] ),
IF (
SELECTEDVALUE ( 'Gross Profit Component'[Gross Profit Id] ) IN { 2, 8, 9, 10 },
b,
a
)
) -------EURO-------
VAR _a_EUR =
(
CALCULATE (
SUM ( GP_LVL_HFB_AFT_SDM_PBI_V[Value EUR] ),
ALL ( 'Gross Profit Component' ),
'Gross Profit Component'[Gross Profit Id] = 1
)
)
VAR _b_EUR =
(
CALCULATE (
SUM ( 'GP_LVL_HFB_AFT_SDM_PBI_V'[Value EUR] ),
ALL ( 'Gross Profit Component' ),
'Gross Profit Component'[Gross Profit Id] = 2
)
)
VAR _d_EUR =
DIVIDE (
SUM ( 'GP_LVL_HFB_AFT_SDM_PBI_V'[Value EUR] ),
IF (
SELECTEDVALUE ( 'Gross Profit Component'[Gross Profit Id] ) IN { 2, 8, 9, 10 },
_b_EUR,
_a_EUR
)
)
RETURN
IF (
ISFILTERED ( 'Product Range'[PA] ) || ISFILTERED ( 'Product Range'[PRA] ),
IF (
SELECTEDVALUE ( Currency_Filter[Currency] ) = "EUR",
_d_EUR_PA,
IF (
HASONEVALUE ( Store[Retail Unit Name] )
|| HASONEVALUE ( 'Primary Market Area'[Retail Unit Name] ),
_d_PA,
""
)
),
IF (
SELECTEDVALUE ( Currency_Filter[Currency] ) = "EUR",
_d_EUR,
IF (
HASONEVALUE ( Store[Retail Unit Name] )
|| HASONEVALUE ( 'Primary Market Area'[Retail Unit Name] ),
d,
""
)
)
)

--------------------------------------------------------------------------

GP_LVL_HFB_AFT_SDM_PBI_V and GP_LVL_FCT_SDM_PBI_V are my fact tables.

--------------------------------------------------------------------------------------------

From above screenshot, there both sides slicer filters are identical , means all are coming from same respective tables and we are just comparing them left table vs right table based on
Slicer selections.

Now I want to show one more table visual where it has their(Left table vs right table) difference.

For eg., See one more screenshot attached below, where I have provided few inputs and extracted the data. Now the third table should show the output as follows:

01.Sales Budgeted Price = 2615 -467 = 2148 and GM % = 100% -100% =0
03.GP0 GM0 = 788- 192 = 596 and GM%= 30.12 %- 41.11 % = -10.99 %
and so on

Need your help, how I can achieve this.

Thanks!

Re: Difference between two identical tables with multiple filters

V-lianl-msft — Thu, 24 Mar 2022 05:39:00 GMT

Hi @Anonymous ,

I guess you set up visual interaction with none in both slicers.

Maybe you should consider creating a disconnected calendar table as the slicer of Table 2, and creating a measure to bind to the custom calendar table for calculation through filter conditions. This makes it easy to calculate the difference in Table 1.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

topic Difference between two identical tables with multiple filters in Developer

Difference between two identical tables with multiple filters

Re: Difference between two identical tables with multiple filters