Filter Visual by unique sale $ OR cumulative fiscal year $

[AH2022]

Hi All,

The visual displays clients (rows) and their purchases (values) by displayed fiscal years (columns).

The client must be able to filter the visual by Selected level (ex: 5k-15k) and display only the clients having:

• A purchase between 5k-15k OR
• Amount sales by Fiscal year (at least ONE of displayed fiscal years), between 5k-15k

I use a table for the levels, and a measure to match the selected value.

I filter the visual when measure is =1

1If$inSelectedInterval = IF(and([SalesAmountUniqORFY]>=SELECTEDVALUE(LevelTable[Min]), [SalesAmountUniqORFY]<=SELECTEDVALUE(LevelTable[Max])), 1, 0)

How do I write the [SalesAmountUniqORFY] measure, in order to include the levels for unique purchase, or the displayed fiscal year ? Or maybe there is another solution.

The visual should show customers who meet the criteria, and ALL their purchases (even those not in the chosen criteria) ex client 2 FY 21-22 = 5k, and 23-24 we can see a purchase of 300$

Please help to identify a solution for this one.

Thanks in Advance.

[AH2022]

Thank you @Jihwan_Kim ,

Why it displays when condition is = 0 ?

The amountneed to be at least one FY in the selected level (not SUM of all FY amount).  Thank you!

[Jihwan_Kim]

Hi, I am not sure if I understood your question correctly, but if you want to not-show zero, please try to remove zero part in the measure, something like below.

Condition: = 
VAR _conditionone =
    AND (
        [sales amount:] >= MIN ( 'level'[min] ),
        [sales amount:] <= MAX ( 'level'[max] )
    )
VAR _conditiontwoamount =
    CALCULATE ( [sales amount:], REMOVEFILTERS ( 'item'[item] ) )
VAR _conditiontwo =
    AND (
        _conditiontwoamount >= MIN ( 'level'[min] ),
        _conditiontwoamount <= MAX ( 'level'[max] )
    )
RETURN
    IF (
        CALCULATE ( [sales amount:], REMOVEFILTERS ( 'year'[year] ) ) <> BLANK (),
        IF ( OR ( _conditionone, _conditiontwo ), 1 )
    )

[AH2022]

Hi @Jihwan_Kim ,

This line should not be displayed, as by SINGLE fiscal year, we are under 15K. I am under the impression that using removefilters for the item, it ignores the implicit context, and he considers 5,000$ ( of FY2) + 13,000$(of FY4) = 18,000$ (between 15K-25K).

We want to display only if there is a Unique Item between 15K-25k OR at least ONE SINGLE Fiscal Year between 15K-25K (and not the SUM of all displayed fiscal years).

Thank you so much for help !

This code answers the question :

UniqueSaleORSingleFY =

VAR _minLevel = SELECTEDVALUE('level'[min])

VAR _maxLevel = SELECTEDVALUE('level'[max])

-- Check if any single purchase is within the range

VAR _uniquePurchaseCondition =

    CALCULATE (

        COUNTROWS ( 'sales' ),

        FILTER ( 'sales', [sales amount:] >= _minLevel && [sales amount:] <= _maxLevel )

    ) > 0

-- Check if total purchases in at least one fiscal year are within the range

VAR _fiscalYearTotalCondition =

    CALCULATE (

        SUM ( sales[amount] ),

        REMOVEFILTERS ( 'item'[item] ),

        VALUES ( 'year'[year] )  -- Ensure fiscal year is respected

    )

VAR _isFiscalYearInRange =

    CALCULATETABLE (

        SUMMARIZE (

            'sales',

            'year'[year],

            "TotalSales", SUM ( sales[amount] )

        ),

        'year'[year]  -- Filter by each fiscal year

    )

VAR _isInFiscalYearRange =

    COUNTROWS (

        FILTER (

            _isFiscalYearInRange,

            [TotalSales] >= _minLevel && [TotalSales] <= _maxLevel

        )

    ) > 0

RETURN

    IF ( _uniquePurchaseCondition || _isInFiscalYearRange, 1, 0 )

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

sales amount: = 
SUM( sales[amount] )

Condition: =
VAR _conditionone =
    AND (
        [sales amount:] >= MIN ( 'level'[min] ),
        [sales amount:] <= MAX ( 'level'[max] )
    )
VAR _conditiontwoamount =
    CALCULATE ( [sales amount:], REMOVEFILTERS ( 'item'[item] ) )
VAR _conditiontwo =
    AND (
        _conditiontwoamount >= MIN ( 'level'[min] ),
        _conditiontwoamount <= MAX ( 'level'[max] )
    )
RETURN
    IF (
        CALCULATE ( [sales amount:], REMOVEFILTERS ( 'year'[year] ) ) <> BLANK (),
        IF ( OR ( _conditionone, _conditiontwo ), 1, 0 )
    )

[lbendlin]

You need to create a measure for that and then use the measure as a visual level filter.

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.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...