Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Data comparison from one column / slicer

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 t...
Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    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

jolind1996's avatar
jolind1996
Icon for Resolver II rankResolver II
2 years ago

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