Display Issue

[Anonymous]

Hi All,

I am trying to show difference between average of top 3 customers value and customer value selected in slicer for a particular selected quarter using below formula which is working fine.

The challenge I am facing is if the selected Customer is in top 3 or if the differnce we derived is a negative value then we should not display the result

var y = CALCULATE( SUMX(TOPN(3, ALLSELECTED(Master[Customer Name]), [Total_value], DESC),Master[Total_value]),ALL(Master[Broker Name]))

var x =
IF (
SELECTEDVALUE ( Master[Customer Name] )
= SELECTEDVALUE (Master[Customer Name]),
CALCULATE (
VAR _seleYear =
SELECTEDVALUE ( 'Slicer Table'[Year] )
VAR _seleQuar =
SELECTEDVALUE ( 'Slicer Table'[Quarter] )
RETURN
IF (
SELECTEDVALUE( Master[Year] ) = _seleYear
&& SELECTEDVALUE( Master[Quarter]) = _seleQuar,
SUM ( Master[Total] ))
)
)
return (y/3)-x

Can someone please suggest on how to achieve this.

Thanks in advance

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, your problem is if the selected Customer is in top 3 or if the result is a negative value then return blank. Here's my solution.

Modify the formula.

Measure =
VAR y =
    CALCULATE (
        SUMX (
            TOPN ( 3, ALLSELECTED ( Master[Customer Name] ), [Total_value], DESC ),
            Master[Total_value]
        ),
        ALL ( Master[Broker Name] )
    )
VAR x =
    IF (
        SELECTEDVALUE ( Master[Customer Name] )
            = SELECTEDVALUE ( Master[Customer Name] ),
        CALCULATE (
            VAR _seleYear =
                SELECTEDVALUE ( 'Slicer Table'[Year] )
            VAR _seleQuar =
                SELECTEDVALUE ( 'Slicer Table'[Quarter] )
            RETURN
                IF (
                    SELECTEDVALUE ( Master[Year] ) = _seleYear
                        && SELECTEDVALUE ( Master[Quarter] ) = _seleQuar,
                    SUM ( Master[Total] )
                )
        )
    )
VAR _Top =
    SELECTCOLUMNS (
        TOPN ( 3, ALL ( 'Master' ), 'Master'[Total_value] ),
        "Customer", [Customer Name]
    )
RETURN
    IF (
        COUNTROWS (
            FILTER ( ALLSELECTED ( 'Master' ), 'Master'[Customer Name] IN _Top )
        ) > 0,
        BLANK (),
        IF ( ( y / 3 ) - x < 0, BLANK (), ( y / 3 ) - x )
    )

As you say, your original formula works partly, I only create a sample to verify the condition "if the selected Customer is in top 3":

Get correct result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523