Remove Average data coming matrix when grouping is applied to it

[Pratha]

Hello Experts,

I have added grouping (+) to my table matrix, but when I do so , the grouped row automatically shows "average" data against each row.

Request is : how can I blank out this average values which are showed against the summed function. 

(in screenshot , I do not want to see values which are striked out in yellow lines, instead a blank row is good)

Please let me know if you need more details. 

 

[v-saisrao-msft · Accepted solution]

Hi @Pratha, 
Below is the PBIX file for your reference, created from the sample data you provided.

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

Thank you.

[v-saisrao-msft]

Hi @Pratha, 
Thank you for reaching out to the Microsoft fabric community forum. Thank you @DataNinja777  for your input on this issue.
 After thoroughly reviewing the details you provided, I was able to reproduce the scenario, I have used it as sample data on my end and successfully implemented it.   

Dax Measure:  

% Display Measure = 
IF (
    ISINSCOPE(PerformanceData[SubFunction]),
    BLANK(),
    AVERAGE(PerformanceData[Percentage])
)

Output: 

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly. 
 
Thank you. 

[Pratha]

Thank you so much for the solution. The only issue with me is that the output table values are from a Measure created using CALCULATE and DIVIDE. I am adding a SAMPLE PowerBI and SAMPLE ppt (screenshot below) for more description and information. Please can you go through them and help. I have created a sample powerbi file but no idea how to upload it for you. Please help

  

 

[v-saisrao-msft]

Hi @Pratha,

To assist you better and replicate the issue accurately, could you please provide either:

• A sample PBIX file, or
• A small set of sample data that reflects your scenario and the expected output?

This will help us understand and suggest a precise solution to your issue.

Thank you.

[Pratha]

Hello Sir, Sorry for the delay. took a longer time to upload. Here is the link. 

https://drive.google.com/drive/folders/14sOFNgcajAvPjT4tnyerJ0xSBocLzGU8?usp=sharing 

I have uploaded a jpg with short brief of my requirement, excel file with sample data and powerbi . Please let me know if you have any access issues or need more information. Thank you for your support

[v-saisrao-msft · Accepted solution]

Hi @Pratha, 
Below is the PBIX file for your reference, created from the sample data you provided.

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

Thank you.

[Pratha]

Thank you so much sir. I liked the table matrix now and the display is exactly what I am looking for but one question : when I click on the "-" for "ABC" to group them, the complete ABC rows get hidden and so does XYZ if I group it. Is this some issue with PowerBI version? Please advice 

[v-saisrao-msft · Accepted solution]

Hi @Pratha,

This is the expected behaviour of Power BI. When there is no data, all the rows get hidden. This is not an issue with the version but rather with how it functions.

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

Thank you.

[Ashish_Excel]

Hi,

There are lots of measures i see written in that file.  A lot of them are unrequired and you have had to create them because of a non-optimal source data structure.  We would have to unpivot that to just get started.  Before i write the measures for you, I just wanted to understand your overall objective.  From the Source sheet tab in the MS Excel file, explain clearly what you are trying to achieve.

[v-saisrao-msft]

Hi @Pratha,
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.

[DataNinja777]

Hi @Pratha ,

To remove the average values that appear when grouping is applied in a Power BI matrix, you can modify your DAX measures to return a blank value when the matrix is at the group (subtotal) level. The issue occurs because Power BI automatically calculates an average or other aggregation for subtotal rows, even if you don’t want it to. To prevent this, you can use the ISINSCOPE function in your measure to check if the specific column used for grouping is currently in scope. If it is, the measure returns the original value; if not, it returns blank. This ensures that only detail rows show values, and subtotal rows remain empty.

Here's the syntax:

YourMeasure :=
IF(
    ISINSCOPE('YourTable'[FunctionColumn]),
    [YourOriginalMeasure],
    BLANK()
)

Replace 'YourTable'[FunctionColumn] with the actual column used in your "Summed Functions" grouping. Also, [YourOriginalMeasure] should be the expression or measure you originally used to calculate values for each quarter or period. You’ll need to apply this logic to each relevant measure (Q1, Q2, Q3, Q4, H1, H2, FY) to suppress the subtotal values across the entire matrix.

Best regards,

[Pratha]

Thank you Sir