Need DAX for filter matrix data for second level group by total based on search number input box val

[V_Ramugade]

Hi All,

I am working on the power bi report where requirement is, there is slicer called "Select View" contains values like "Total" & "Sub Total"  and another numeric parameter slicer to get the user input into the slicer and then filter matrix visual based on the select view option and provide data which is greater than the slicer inputed value. 

Now, report is working for first level of total but not working for second level of total i.e. sub total.
And I want measure that work for both total and sub total level and filter matrix data.

Below are the screen shot for the reference.

1. Numeric Parameter slicer 

2. Filter Matrix Data measure.

3. Now actual issue, As per below screen shot where you can see the input slicer has value 6 and select view is "Sub Total" and first matrix visual filter individual line of total for "Product2" And Location "Japan". but I want it should not filter individual row for Product 2 and Japan location instead it should consider the "Sub Total" as a 15 for Japan location and keep that row into the matrix.
I want matrix data should be filter at Location "Sub Total" row level without removing any field from the below mentioned matrix details. Matrix 2 & 3 is for reference to understand what happening at Matrix 1 and what we want is in Matrix 3 where I didn't applied any visual level filter.

Test.PBIX for your reference.

Thanks in advance for best possible solution.
Regards,

Vishal Ramugade.

[v-sdhruv]

Hi @V_Ramugade ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please Accept it as a solution so other members can easily find it.
Thank You

[v-sdhruv]

Hi @V_Ramugade ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please Accept it as a solution so other members can easily find it.
Thank You

[v-sdhruv]

Hi @V_Ramugade ,
Just wanted to check if you had the opportunity to review the suggestion provided?
If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

[v-sdhruv]

Hi @V_Ramugade ,

You can try this DAX-

Show Row Flag =
VAR SelectedView = SELECTEDVALUE('Select View'[View]) -- "Total" or "Sub Total"
VAR Threshold = SELECTEDVALUE('ThresholdValue'[Value]) -- User input

VAR GroupLevelTotal =
SWITCH(
SelectedView,
"Sub Total", CALCULATE(SUM('Sales'[Amount]), ALLEXCEPT('Sales', 'Sales'[Location])),
"Total", CALCULATE(SUM('Sales'[Amount]), ALL('Sales')),
BLANK()
)

RETURN
IF(GroupLevelTotal > Threshold, 1, 0)

This measure calculates the subtotal per Location or overall total, depending on the selected view.
Then apply visual level filter and set it to 1.
This will keep the detailed rows, but only for groups where the group subtotal or total exceeds the threshold.
Hope this helps!

[Anonymous]

Hi @V_Ramugade 

Could you please provide pbix so we can better help you? How to provide sample data in the Power BI Forum - Microsoft Fabric CommunityHow to provide sample data in the Power BI Forum - Microsoft Fabric Community Please remove any sensitive data in advance.

Best Regards,
Yulia Xu

[V_Ramugade]

Hi @Anonymous ,

Please have a look on below attached PBIX.

Test.PBIX