Forum Discussion

Gerbil's avatar
Gerbil
Icon for Helper I rankHelper I
2 years ago
Solved

Creating a Card that displays a count of aggregate data?

Reposting this with clearer table   So I have a table like so:   Region      Name      Flag      Count of Job ID North John On 12 South Drake Off 11 South Jason On 5 We...
Need Help
  • kpost's avatar
    kpost
    2 years ago
    Try this:
     
    rowcount =
    VAR table1 = CALCULATETABLE(SUMMARIZE('Table', 'Table'[Flag], 'Table'[Region], 'Table'[Name], "@rowcount", COUNTROWS('Table')), 'Table'[Flag] = "Off")
    RETURN
    COALESCE(SUMX(table1, IF([@rowcount] > 10, 1, 0)), 0)
     
     
    ///Mediocre Power BI Advice, but it's free///
Gerbil's avatar
Gerbil
Icon for Helper I rankHelper I
2 years ago

So the data coming in (from direct query) looks like this:

 

Region          Name          Flag          Job ID     
NorthJohnOn16127
NorthJohnOn77647
NorthJohnOn41588
NorthJohnOn46424
NorthJohnOn24132
NorthJohnOn56180
NorthJohnOn57396
NorthJohnOn41324
NorthJohnOn8899
NorthJohnOn809
NorthJohnOn19558
NorthJohnOn91024
NorthJohnOn90642
SouthDrakeOff52577
SouthDrakeOff76784
SouthDrakeOff81416
SouthDrakeOff77264
SouthDrakeOff53032
SouthDrakeOff25992
SouthDrakeOff50560
SouthDrakeOff20368
SouthDrakeOff81093
SouthDrakeOff97126
SouthDrakeOff46680
SouthDrakeOff59402
SouthJasonOn93735
SouthJasonOn24804
SouthJasonOn8312
SouthJasonOn96618
SouthJasonOn5066

...etc

 

And the table created above has the column "Count of Job ID" through Power BI. "Count of Job ID" is not an actual column, it's more like a measure. It's an aggregate count of "Job ID". So I can't use the function you've provided since "Count of Job ID" is not a column in my table.

kpost's avatar
kpost
Icon for Solution Sage rankSolution Sage
2 years ago
Try this:
 
rowcount =
VAR table1 = CALCULATETABLE(SUMMARIZE('Table', 'Table'[Flag], 'Table'[Region], 'Table'[Name], "@rowcount", COUNTROWS('Table')), 'Table'[Flag] = "Off")
RETURN
COALESCE(SUMX(table1, IF([@rowcount] > 10, 1, 0)), 0)
 
 
///Mediocre Power BI Advice, but it's free///
  • Gerbil's avatar
    Gerbil
    Icon for Helper I rankHelper I
    2 years ago

    That totally worked! Thank you so much!