Forum Discussion

gleatherman's avatar
gleatherman
Regular Visitor
2 years ago
Solved

Count of specific values in a visual

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 ...
Need Help
  • gleatherman's avatar
    gleatherman
    2 years ago

    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's avatar
Dangar332
Icon for Resident Rockstar rankResident Rockstar
2 years ago

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's avatar
gleatherman
Regular Visitor
2 years ago

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

 

  • Dangar332's avatar
    Dangar332
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    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))