Issue with Aggregation and Filtering

[jeeva-chandru]

Hello,

I have a DAX formula in Power BI that I need help with. I've merged the AOP and Sales datasets into a combined dataset called AOP_EDW_Merged. This dataset includes columns such as Opportunity ID, Vertical, Subvertical, GBE, Region, Completion Quarter, Completion Year, Orders ($M), SNAPSHOT_DATE, and more. It's a historical dataset with monthly data, and the AOP data remains static for each year.

In my report, I've set up slicers for Vertical, Subvertical, GBE, Region, Completion Quarter, and Completion Year. The matrix visual is configured with Vertical as rows and Region as columns, and the M-AOPTarget measure is placed in the Values section. When I filter by Vertical, the filters seem to function correctly. However, when I filter by Subvertical, GBE, or Region, no values appear, and the results are blank.

M-AOPTarget =

CALCULATE(

    SUMX(

        SUMMARIZE(

            FILTER(

                AOP_EDW_MERGED,

                AOP_EDW_MERGED[VERTICAL] <> "5 - Life Sciences"

            ),

            AOP_EDW_MERGED[VERTICAL],            

            AOP_EDW_MERGED[SUB VERTICAL],

            AOP_EDW_MERGED[GBE],

            Region_Sort_Order[REGION],

            AOP_EDW_MERGED[QUARTER],

            "DistinctOrderAmount",

            SUM(AOP_EDW_MERGED[Orders ($M)])

        ),

        [DistinctOrderAmount]

    ),

    REMOVEFILTERS(AOP_EDW_MERGED[SNAPSHOT_DATE])

)

Appreciate your help & support on this!

Thank you,

Jeeva

[v-saisrao-msft]

Hi @jeeva-chandru,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft]

Hi @jeeva-chandru,

Have you had a chance to review the solution we shared by @dinesh_7780   ? If the issue persists, feel free to reply so we can help further. 

If not 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.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? 

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Thank you.

[dinesh_7780]

Hi @jeeva-chandru ,

If you must aggregate at Quarter/Vertical/Subvertical/GBE/Region level. Try the below measure.

M-AOPTarget =

VAR SummaryTable =

    SUMMARIZE(

        AOP_EDW_MERGED,

        AOP_EDW_MERGED[VERTICAL],

        AOP_EDW_MERGED[SUB VERTICAL],

        AOP_EDW_MERGED[GBE],

        AOP_EDW_MERGED[REGION],

        AOP_EDW_MERGED[QUARTER],

        "OrderAmount", SUM(AOP_EDW_MERGED[Orders ($M)])

    )

RETURN

CALCULATE(

    SUMX(SummaryTable, [OrderAmount]),

    AOP_EDW_MERGED[VERTICAL] <> "5 - Life Sciences",

    REMOVEFILTERS(AOP_EDW_MERGED[SN

APSHOT_DATE])

)

If my response as resolved your issue please mark it as solution and give kudos.

[jeeva-chandru]

@Kedar_Pande Thank you for the response. I have tried but no luck. it's still returning blank. I have tested it just adding these 2 fields in the table and it's blank eventhoug the value exists for that specific sub vertical.

Kind Regards,

Jeeva

[Kedar_Pande · Accepted solution]

@jeeva-chandru 

Please share a simplified version of your PBIX file (in English) without sensitive data. You can upload it to a public cloud service like OneDrive, Google Drive, or Dropbox and share the link. This will help in understanding your data structure and the issue, allowing for more precise guidance.

[Kedar_Pande]

M-AOPTarget =
CALCULATE(
SUM(AOP_EDW_MERGED[Orders ($M)]),
AOP_EDW_MERGED[VERTICAL] <> "5 - Life Sciences",
REMOVEFILTERS(AOP_EDW_MERGED[SNAPSHOT_DATE])
)

Test this - should respond to all slicers correctly.

If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

@jeeva-chandru