Forum Discussion

rksharma86's avatar
rksharma86
Frequent Visitor
3 years ago

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

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.

 

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    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