DAX Table Expression help needed - Unable to remove filter DAX expression

[rksharma86]

Problem: I want to calculate the Complaint per 10k Customer for each member and Industry average

Data Issue:  Some members who reported breach not reported assets and some members who reported assets not reported breach.

As you can see both the Member and Industry is same number on Chart due to Slicer on page.

DAX Code I used to get the Average insudtry per 10 K = 

Industry  per 10k Breach =

VAR TmpTable =

        FILTER (

            SUMMARIZE (

                'Member',

                'Member'[Subscriber],

                "MemberSum",

                    CALCULATE (

                        SUM ( Assets[Customers] ),

                        ALLEXCEPT ( dimCalendar, dimCalendar[Fiscal Year] )

                    ),

                "TotalBreach",

                    CALCULATE (

                        [Breach],

                        ALLEXCEPT ( dimCalendar, dimCalendar[Fiscal Year] ),

                        dimCalendar[FisYear] >= 2021

                    )

            ),

            AND ( NOT ISBLANK ( [MemberSum] ), NOT ISBLANK ( [TotalBreach] ) )

        )

    VAR MemSum =

        SUMX (TmpTable, [MemberSum] )

    VAR TotBrch =

        SUMX (TmpTable, [TotalBreach] )

    RETURN

        DIVIDE ( TotBrch, MemSum ) * 10000

What I want to do with DAX Expression is to calculate the industry per 10k Breach which should not gets filtered by the Slicer. SO that each sub have their own per 10k breack and Industry 10k breach should be same across all the subscriber.

[Anonymous]

Hi @rksharma86 ,  

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you .

Best Regards, 

Liu Yang 

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