Count of specific values in a visual

[gleatherman]

I have a table that has sales entries:

Name | Location | Type | Quantity | Date

Bob  |  Location A | Type 1 | 3 | 1-1-2024

John  |  Location A | Type 2 | 3 | 1-2-2024

Bob  |  Location A | Type 2 | 3 | 1-2-2024

Bob  |  Location A | Type 1 | 4 | 1-1-2024

I have Slicers to select the Location and Dates for the page.

I have a Matrix visual: It has Name in the Rows, Type in the Columns, and sum of quantity as the Values.

So it looks like this:

Name | Type 1 | Type 2 | Total

John | 0 | 3 | 3

Bob | 7 | 3 | 10

Totals | 7 | 6 | 13

I have an existing Card giving me the Count of Distinct Names in the selected Location/Date range.

I would like to add a Card visual that tells me how many distinct Names have less than 33% "Type 1" quantities for the selected location/date range vs their Total quantities.   

So in the example above, John has 0% Type 1, Bob has 70% Type 1; therefore, there should be a count of 1 person below 33% Type 1.

Once I get that... or perhaps instead of that Card... I'd like to create a second page that has a table/matrix visual that summarizes it by Location:  Location|Count of Names|Count of Names below 33% 'Type 1'

Thanks for any help you can give.

[gleatherman · Accepted solution]

I went a different direction.

I created a Measure for Sum of Type 1:

SumType1 = CALCULATE(sum(sales[Quantity]),sales[Type]="Type 1")

 

I created a Measure for the percentage of total for Type 1:

Percent Total = [SumType1] / SUM(sales[Quantity]) * 100

 

And then for the Count of people under a certain percentages I made this Measure using the Summarize function:

 

Count Under 45% =

var _table = SUMMARIZE(sales,sales[Name],"Sales Total",SUM(sales[Quantity]),"Type 1",sales[SumType1],"Percent 1",[Percent Total])

return

COUNTROWS(FILTER(_table,[Percent 1]<45))

Probably could have done it better differently, but if it works, it works!

Now to apply this to my real data.

[Dangar332]

hi, @gleatherman 

try below measure 

Percent of Row Total =
var a = sumx(filter(tablename,tablename[type]="type 1"),tablename[quantity])
var b = calculate(sum(tablename[quantity]),allexcept(tablename,tablename[name]))
var c = divide(a,b)
return
 calculate(count(tablename[name]),c <0.33)

[gleatherman]

I'm getting this error about True/False expressions, am I mistyping? 'sales' is the table name.

[Dangar332]

hi, @gleatherman 

Percent of Row Total =
var a = sumx(filter(tablename,tablename[type]="type 1"),tablename[quantity])
var b = calculate(sum(tablename[quantity]),allexcept(tablename,tablename[name]))
var c = divide(a,b)
return
 calculate(count(tablename[name]),filter(tablename,c <0.33))

[gleatherman]

That was erroring out as [Total Quantity] didn't exist so I had to modify it to calculate total quantity.

That gives me a Percent Total row for each person.

What I need now is a way to count the number of people who have less than a certain percentage.  So in my current example

I'd like to count the number of people who have less than a certain value in the 'PercentTotal' measure and put it on a card next to the matrix.   If I add the 'PercentTotal' measure to a card, I get the 'PercentTotal'of the the totals of the matrix, which is expected, but not what I would like it to show.   

I'm not sure how to evaluate each row of the measure to count the amount of people below a certain percentage.

[amustafa]

Assuming your tabke name is 'Sales', try this measure:

Percent of Row Total =

DIVIDE(

    SUM(Sales[Quantity]),

    CALCULATE(

        [Total Quantity],

        ALLEXCEPT(Sales, Sales[Name])

    )

)