Data comparison from one column / slicer

[genashpuryk]

Hello fellows! I tried to find any possible solution for my problem on the internet, but nothing seems to match my needs. 

I have slicer, where different datasets can be chosen: 

My hope is to create a comparison between 2 chosen datasets, where i can subtract 2024 AC Price from 2023 AC Price, for example. Is it possible to create this type of calculation? If so, share your knowledge with me, please. Thank you! 

[Anonymous · Accepted solution]

Hi @genashpuryk ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Qty = 
VAR _minsource =
    MIN ( 'OPI_Database'[Source ID] )
VAR _maxsource =
    MAX ( 'OPI_Database'[Source ID] )
VAR _minqty =
    CALCULATE (
        SUM ( 'QCPW Data'[Quantity] ),
        FILTER ( 'QCPW Data', 'QCPW Data'[Source ID] = _minsource )
    )
VAR _maxqty =
    CALCULATE (
        SUM ( 'QCPW Data'[Quantity] ),
        FILTER ( 'QCPW Data', 'QCPW Data'[Source ID] = _maxsource )
    )
RETURN
    IF (
        ISINSCOPE ( 'OPI_Database'[Source.Name] ),
        SUM ( 'QCPW Data'[Quantity] ),
        _maxqty - _minqty
    )

SPrice = 
VAR _minsource =
    MIN ( 'OPI_Database'[Source ID] )
VAR _maxsource =
    MAX ( 'OPI_Database'[Source ID] )
VAR _minprice =
    CALCULATE (
        SUM ( 'QCPW Data'[Price] ),
        FILTER ( 'QCPW Data', 'QCPW Data'[Source ID] = _minsource )
    )
VAR _maxprice =
    CALCULATE (
        SUM ( 'QCPW Data'[Price] ),
        FILTER ( 'QCPW Data', 'QCPW Data'[Source ID] = _maxsource )
    )
RETURN
    IF (
        ISINSCOPE ( 'OPI_Database'[Source.Name] ),
        SUM ( 'QCPW Data'[Quantity] ),
        _maxprice - _minprice
    )

Best Regards

[genashpuryk]

Thank you so much! That absolutely worked for me with some minor changes.

[nattanarcilla]

You can try to explore calculation group since you are using a matrix visual.
I found this video very helpful (calculation group)

[jolind1996]

Hello Genashpuryk,

You can create a measure that calculates the difference between the values of two datasets selected using a slicer. Here is an example of how you can do this:

Difference =
VAR SelectedDatasets = VALUES('Table'[Column1])
VAR Dataset1 = SELECTEDVALUE(SelectedDatasets, BLANK())
VAR Dataset2 = SELECTEDVALUE(SelectedDatasets, BLANK())
VAR Value1 = CALCULATE(SUM('Table'[Value]), 'Table'[Column1] = Dataset1)
VAR Value2 = CALCULATE(SUM('Table'[Value]), 'Table'[Column1] = Dataset2)
RETURN Value1 - Value2

In this measure, 'Table' is the name of the table containing the data, 'Table'[Column1] is the column containing the names of the datasets, and 'Table'[Value] is the column containing the values of the datasets. The measure uses the VALUES and SELECTEDVALUE functions to get the names of the datasets selected using the slicer, and the CALCULATE function to calculate the sum of the values for each dataset. The measure then calculates the difference between the values of the two selected datasets.

Please note that you will need to replace 'Table', 'Table'[Column1], and 'Table'[Value] with the actual names of your table and columns. Also, make sure that the slicer is correctly set up to filter the 'Table'[Column1] column.

Best regards,

Johannes

[genashpuryk]

Thank you! That should be exactly what i want!

But seems like i have a syntax problem. Can't find it's place 😣

[jolind1996]

May this help?

Column cannot be found or may not be used in this ... - Microsoft Fabric Community

[jolind1996]

It is possible to create a comparison between two chosen datasets in Power BI. You can do this by creating a measure that calculates the difference between the two datasets. Here is an example of a measure that calculates the difference between the values of two columns, representing two different datasets:

Difference = 
SUMX(
    FILTER(
        'Table',
        'Table'[Column1] = "2024 AC Price"
    ),
    'Table'[Value]
) - SUMX(
    FILTER(
        'Table',
        'Table'[Column1] = "2023 AC Price"
    ),
    'Table'[Value]
)

In this measure, 'Table' is the name of the table containing the data, 'Table'[Column1] is the column containing the names of the datasets, and 'Table'[Value] is the column containing the values of the datasets. The measure calculates the sum of the values for the "2024 AC Price" dataset and subtracts the sum of the values for the "2023 AC Price" dataset.

Please note that you will need to replace 'Table', 'Table'[Column1], and 'Table'[Value] with the actual names of your table and columns. Also, make sure that the names of the datasets in the measure match the names of the datasets in your table.

Best regards,

Johannes

[genashpuryk]

Thank you, Johannes! 
I understand your logic, but my goal is to make datasets variable using slicer, so that i can compare not only 2024 AC and 2023 AC, but 2024 OB and RF3 as well.