Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MSH
Frequent Visitor

How to get values based on multiple slicers for mutiple conditions

Dear Experts,

Can some one helpto resolve this, I have a table with few colums, main are Country , Products, Year, Month, Sales QTY, Value. and two table filters, one to select the year table filter named Filter[Year], another one to select the Type either Qty or value, name Filter[Type]

Capture.JPGFilter1.JPGFilter2.JPG

 

 

 

 

what is required is when user selects,

select QTY, and year 2020 & 2019 then sum of qty2020/qty2019

select QTY, and year 2021 & 2020 then sum of qty 2021/qty2020

select QTY, and year 2021 & 2019 then sum of qty 2021/qty2019

if all three years selected, then 0 or any message

 

similary id Value is selected

Select Value2, and year 2020 & 2019 then sum of value2020/value 2019

Select value2, and year 2021 & 2020 then sum of value2021/value 2020

Select value2, and year 2021 & 2019 then sum of value 2021/value 2019

if all three years selected, then 0 or any message.

 

can some experts please help me

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @MSH ,

According to my understand , you want to calculate the QTY/Value of the larger year divide the previous year, right?

You could use the following formula after unpivot the QTY and Value columns:

10.19.2.1.gif

Measure =
VAR _seleYear =
    ALLSELECTED ( 'Table'[Year] )
VAR _QTYOrValue =
    SWITCH (
        SELECTEDVALUE ( 'Table'[Attribute] ),
        "QTY", CALCULATE ( SUM ( 'Table'[Value.1] ), 'Table'[Attribute] = "QTY" ),
        "Value", CALCULATE ( SUM ( 'Table'[Value.1] ), 'Table'[Attribute] = "Value" )
    )
VAR _seleAttribute =
    SELECTEDVALUE ( 'Table'[Attribute] )
VAR _countSelectedItems =
    CALCULATE ( COUNTROWS ( FILTERS ( 'Table'[Year] ) ), _seleYear )
VAR _thelargerYear =
    CALCULATE (
        MAX ( 'Table'[Year] ),
        FILTER ( 'Table', 'Table'[Year] IN _seleYear )
    )
VAR _thePreviousYear =
    CALCULATE (
        MIN ( 'Table'[Year] ),
        FILTER ( 'Table', 'Table'[Year] IN _seleYear )
    )
RETURN
    IF (
        _countSelectedItems = 2,
        CALCULATE (
            SUM ( 'Table'[Value.1] ),
            FILTER (
                'Table',
                'Table'[Attribute] = _seleAttribute
                    && 'Table'[Year] = _thelargerYear
            )
        )
            / CALCULATE (
                SUM ( 'Table'[Value.1] ),
                FILTER (
                    'Table',
                    'Table'[Attribute] = _seleAttribute
                        && 'Table'[Year] = _thePreviousYear
                )
            ),
        0
    )

 My visualization looks like this:10.19.2.2.PNG

Here is the pbix file.

 

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

View solution in original post

2 REPLIES 2
v-eqin-msft
Community Support
Community Support

Hi @MSH ,

According to my understand , you want to calculate the QTY/Value of the larger year divide the previous year, right?

You could use the following formula after unpivot the QTY and Value columns:

10.19.2.1.gif

Measure =
VAR _seleYear =
    ALLSELECTED ( 'Table'[Year] )
VAR _QTYOrValue =
    SWITCH (
        SELECTEDVALUE ( 'Table'[Attribute] ),
        "QTY", CALCULATE ( SUM ( 'Table'[Value.1] ), 'Table'[Attribute] = "QTY" ),
        "Value", CALCULATE ( SUM ( 'Table'[Value.1] ), 'Table'[Attribute] = "Value" )
    )
VAR _seleAttribute =
    SELECTEDVALUE ( 'Table'[Attribute] )
VAR _countSelectedItems =
    CALCULATE ( COUNTROWS ( FILTERS ( 'Table'[Year] ) ), _seleYear )
VAR _thelargerYear =
    CALCULATE (
        MAX ( 'Table'[Year] ),
        FILTER ( 'Table', 'Table'[Year] IN _seleYear )
    )
VAR _thePreviousYear =
    CALCULATE (
        MIN ( 'Table'[Year] ),
        FILTER ( 'Table', 'Table'[Year] IN _seleYear )
    )
RETURN
    IF (
        _countSelectedItems = 2,
        CALCULATE (
            SUM ( 'Table'[Value.1] ),
            FILTER (
                'Table',
                'Table'[Attribute] = _seleAttribute
                    && 'Table'[Year] = _thelargerYear
            )
        )
            / CALCULATE (
                SUM ( 'Table'[Value.1] ),
                FILTER (
                    'Table',
                    'Table'[Attribute] = _seleAttribute
                        && 'Table'[Year] = _thePreviousYear
                )
            ),
        0
    )

 My visualization looks like this:10.19.2.2.PNG

Here is the pbix file.

 

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

Thank you so much,

 

it worked

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.