Measure to determine a variance between two other ...

Mike35 · ‎02-20-2025

Hi all,

Hoping someone can help. I have two sets of data which I have imported into PowerBI, both with corresponding measures created to calculate a score percentage. Effectively - the number of "FC" results divided by the number of total results. I then have a slicer that determines date selection, a second slicer that determines the type (Phone or Email), and a final slicer which allows the user to select the area (A, B, C or D) to give a score percentage based on the area and the date selection, or overall if no selection made.

One of the requirements posed it the user wants to compare the outcome of data set 1, and data set 2 to understand the variance of the score percentages. In theory, the result of data set 1 for a given date and area should be roughly the same as that in data set 2. If the score for the date/area selection in data set 1 is greater or less than 10% difference of that in data set 2, we're looking to have a visualisation that calls this out, reading "Match" or "Mismatch".

A secondary requirement on another page, is to have the same as the above but built into the calculate the "type" as well, so for example if the slicer reads 01/02/2025, the area slicer is selected to "B", is there a measure that can calculate the variance of score of "Phone" between the two data sets, and likewise the same for "Email"

Data set examples-

Set 1:

Set 2:

Thanks

lbendlin · ‎02-20-2025

You cannot measure a measure directly. Either materialize it first, or create a separate measure that implements the entire business logic.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Anonymous · ‎02-20-2025

Thanks for the reply from lbendlin , please allow me to add some more information:
Hi @Mike35 ,

Here are the steps you can follow：

1. Create a table containing the sets to be sliced in Tables 1 and 2.

Slicer_Table =
VAR _table1 =
    SUMMARIZE ( 'Table1', [Date], [Type], [Area] )
VAR _table2 =
    SUMMARIZE ( 'Table2', [Date], [Type], [Area] )
RETURN
    DISTINCT ( UNION ( _table1, _table2 ) )

2. Create measure.

Measure1 =
VAR _selectdate =
    SELECTCOLUMNS ( 'Slicer_Table', "date", [Date] )
VAR _selecttype =
    SELECTCOLUMNS ( 'Slicer_Table', "type", [Type] )
VAR _selectarea =
    SELECTCOLUMNS ( 'Slicer_Table', "area", [Area] )
VAR _sumall =
    SUMX ( ALL ( 'Table2' ), [Value] )
VAR _value1 =
    SUMX (
        FILTER (
            ALL ( 'Table1' ),
            'Table1'[Date]
                IN _selectdate
                    && 'Table1'[Type]
                    IN _selecttype
                        && 'Table1'[Area] IN _selectarea
        ),
        [Value]
    )
RETURN
    IF (
        HASONEVALUE ( 'Slicer_Table'[Date] ) = FALSE ()
            && HASONEVALUE ( 'Slicer_Table'[Type] ) = FALSE ()
            && HASONEVALUE ( 'Slicer_Table'[Area] ) = FALSE (),
        _sumall,
        DIVIDE ( _value1, _sumall )
    )

Measure2 =
VAR _selectdate =
    SELECTCOLUMNS ( 'Slicer_Table', "date", [Date] )
VAR _selecttype =
    SELECTCOLUMNS ( 'Slicer_Table', "type", [Type] )
VAR _selectarea =
    SELECTCOLUMNS ( 'Slicer_Table', "area", [Area] )
VAR _sumall =
    SUMX ( ALL ( 'Table2' ), [Value] )
VAR _value2 =
    SUMX (
        FILTER (
            ALL ( 'Table2' ),
            'Table2'[Date]
                IN _selectdate
                    && 'Table2'[Type]
                    IN _selecttype
                        && 'Table2'[Area] IN _selectarea
        ),
        [Value]
    )
RETURN
    IF (
        HASONEVALUE ( 'Slicer_Table'[Date] ) = FALSE ()
            && HASONEVALUE ( 'Slicer_Table'[Type] ) = FALSE ()
            && HASONEVALUE ( 'Slicer_Table'[Area] ) = FALSE (),
        _sumall,
        DIVIDE ( _value2, _sumall )
    )

Test =
IF(
    DIVIDE([Measure1]- [Measure2],[Measure2]) >=0.1,"Mismatch","Match")

3. Result:

If you want to calculate the variance, you can use the following dax:

VARX.S function (DAX) - DAX | Microsoft Learn

VARX.P function (DAX) - DAX | Microsoft Learn

Best Regards,

Liu Yang

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

Mike35 · ‎02-25-2025

Hi @Anonymous - thank you for your efforts but I am confused on the output of the DAX you have demonstrated. I do not understand how the scores have been calculated because there does not appear to be any Score columing in the table. For example if I manually filter my data to 3rd Feb, Area B & Email as per the final output, set 1 should show no score because there were no entries that match this criteria, whereas set 2 would show 100% as there were 2 x FC entries in the set.

@lbendlin Thanks for your pointers and the links to help provide sample data. I have included both data sets here. To hopefully keep it more simple, I have already added filters to the measures quoted below to include Area & Type in the division calculation.

Internal data set:

Date	Area	Type	Score
01/02/2025	A	Phone	FC
01/02/2025	B	Email	FC
01/02/2025	A	Phone	NC
01/02/2025	C	Email	FC
01/02/2025	A	Phone	FC
01/02/2025	B	Email	NC
01/02/2025	D	Phone	FC
02/02/2025	A	Email	FC
02/02/2025	B	Phone	FC

Internal Score measure:

Internal Compliance = 
DIVIDE(
    CALCULATE(
        COUNTROWS('Internal_Phone_Email_Test'),
        'Internal_Phone_Email_Test'[Area] = "B", 'Internal_Phone_Email_Test'[Type] = "Email", 'Internal_Phone_Email_Test'[Score] = "FC" || 'Internal_Phone_Email_Test'[Score] = "CID"
    ),
    CALCULATE(
        COUNTROWS('Internal_Phone_Email_Test'),
        'Internal_Phone_Email_Test'[Area] = "B", 'Internal_Phone_Email_Test'[Type] = "Email"
    )
)

External data set:

Date	Area	Type	Score
01/02/2025	B	Phone	FC
01/02/2025	C	Email	FC
01/02/2025	D	Phone	FC
01/02/2025	A	Email	FC
01/02/2025	A	Phone	FC
01/02/2025	B	Email	FC
01/02/2025	D	Phone	FC
01/02/2025	C	Email	FC
01/02/2025	C	Phone	NC

External compliance measure:

External Compliance = 
DIVIDE(
    CALCULATE(
        COUNTROWS('External_Phone_Email'),
        'External_Phone_Email'[Area] = "B", 'External_Phone_Email'[Type] = "Email", 'External_Phone_Email'[Score] = "FC" || 'External_Phone_Email'[Score] = "CID"
    ),
    CALCULATE(
        COUNTROWS('External_Phone_Email'),
        'External_Phone_Email'[Area] = "B", 'External_Phone_Email'[Type] = "Email"    
    )
    )

Desired output will effectively be controlled by a slicer for date with the card in the centre showing as Match if both measures are within 10% variance, "Mismatch" if >10% variance.

Thanks