CODEWITHSUNDEEP
powerbidaxexcel-2016
Henrietta Martingale
Henrietta Martingale

Reputation: 891

dax aggregate sums and counts that ignore only certain parts of the filter context

I have this table here:

example table

and I wanted to make a table that shows how many people in a company have an aggregate score greater than 8 for example. So I used this formula:

ScoreOver8 =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( activity, activity[person] ),
            "engagement", CALCULATE ( SUM ( activity[score] ) )
        ),
        [score] > 8
    )
)

And this is the result:

result from formula

Now I want to add another level so I can do this:

one level down

and show which types the people that have a total score greater than 8 participated in even if they didn't earn all of that score in that type. This isn't what happens. What happens is:

Real result one level down

Because while on the aggregate company level there is a person in each company that earned more than 8 points, they don't necessarily earn those 8 points in any one task.

So my question is how do I make the metric so it only cares about the filter context at the company level but not at the type level so I can do what I want.

I'm using excel 2016. I know there are a bunch of fancy new equations in power bi. Alas. This is why I'm still at the addcolumns summarize syntax. I wonder if I can do something like:

CALCULATE ( SUM ( activity[score] ), FILTER ( ALL ( [tasks] ), ... ) )

Too bad I only thought of trying this after I wrote out the whole question. I'll leave it. Maybe someone else has it.

Upvotes: 0

Views: 411

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

You should be able to remove the type filter with ALL (or equivalently, REMOVEFITLERS).

ScoreOver8 =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( activity, activity[person] ),
            "engagement", CALCULATE ( SUM ( activity[score] ), ALL ( activity[type] ) )
        ),
        [score] > 8
    )
)

Upvotes: 0

Related Questions