cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Matrix Table Visual - Omitting Data Less Than 10 Within Individual Cells, Not Totals

Hello, 

 

I am working with a matrix table and am trying to omit data based on the cell count that is dependent on the interaction of my row (gender and ethnicity), column (year) and value (participated). 

 

Here is my current configuration for my visualization:

Rows = (a) Gender; (b) Ethnicity

Columns = (a) Year (2017-18, 2018-19, 2019-20) 

Values = (a) # of Participants (variable = integer [0 = No, 1 = Yes])

 

Here is a sample of my matrix:

 

powerwrangler_0-1600969770598.png

 

I would like for the display to omit any values less than 10 (for example: 7, 6, 5, 8). When I put a filter on # of Participants >= 10, all the figures are still displaying because, I suspect, that it is reading this based on the total (23, 559, and 27) instead of the cell counts (7, 6, 10, 165, 215, 179, 5, 14, 8).

 

Is there a way to omit the 7, 6, 5, and 8?

 

 

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Try like

sumx(filter(summarize(Table, Table[Gender],Table[Ethnicity],"_1", [# of Participants]),[_1]>=10),[_1])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Hello, 

 

Thanks for your repsonse. I am having 2 issues with creating this New Measure (assuming this is what I am supposed to create).

 

Here is what I've got:

NewVariable= sumx(filter(summarize(Table, Table[Gender], Table[Ethnicity], "_1", [Ethnicity], [_1]>=10), [_1]))

 

- My option to select [# of Participants] is not an option. I have the option to choose [Gender] or [Ethnicity], however?

- "Too few arguments were passed to the SUMX function. The minimum argument count for the function is 2.

 

Once this new measure is created, where am I supposed to put it? Is it just a filter on the visual?

 

 

Highlighted
Community Support
Community Support

Hi, @powerwrangler 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

e1.png

 

You may create a measure as below.

Result = 
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Gender],
        'Table'[Ethnicity],
        'Table'[Year],
        "Result",
        IF(
            SUM('Table'[Value])<10,
            BLANK(),
            SUM('Table'[Value])
        )
    ),
    [Result]
)

 

Result:

e2.png

 

Best Regards

Allan

 

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

Highlighted

Hi,

 

Thanks for your help. I should provide more detail. The solution you have offered works and I think I understand how it works. You can see below that now my count of female African-American participants are now omitted from the data because there are less than 10 participants. 

 

participant count.gif

The details that I did not provide is that I have multiple tables that include calculations. Sometimes the # of participants ('Table'[Value] in your case) are needed in the Values of my matrix, but in most other cases, they are not.

 

So, for example, in addition to table that showed # of participants by gender and ethnicity, I have tables that show the percentage of those who ordered a hamburger by gender and ethnicity (Hamburger_Rate = DIVIDE(sum('Table'[Hamburger], sum('Table'[Value2]))).

 

However, the Hamburger_Rate is calculated by dividing by the # of Non-Vegetarians (denoted as 'Table'[Value2]), not # of Participants ('Table'[Value]). However, I would like to only display it if 'Table'[Values] < 10, NOT 'Table'[Value2]< 10.

 

In other words, this is what I have now:

 

hamburger rate.gif

 

In this instance, I would to omit African-american female percentages to parallel my first table because their 'Table'[Value] < 10. I would like all my table to rely on 'Table'[Value] < 10, even if I am not using that as my primary Value field in my matrix.

 

Ideally, I would prefer not to create multiple measures (this report will contain many measures and I'd prefer to avoid that for efficiency), but if that is the only solution, then I will make do.

 

As such, if I were to go the measure route, here is what I tried:

 

Result2 = 
SUMX(
    SUMMARIZE(
        'Table',
        'Table'[Gender],
        'Table'[Ethnicity],
        'Table'[Year],
        "Result2",
        IF(
            SUM('Table'[Value])<10,
            BLANK(),
            DIVIDE(sum('Table'[Hamburger], sum('Table'[Value2]))
        )
    ),
    [Result2]
)

 

But am getting the following error: Too many arguments were passed to the IF function. The maximum argument countfor the function is 3.

 

Any ideas to either update this measure and/or a workaround to filter these tables the same way?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors