measure to count number of accounts that meet criteria within a matrix

[scabral]

Hi,

i have the following matrix in Power BI Report:

It using a heirarchy of Company/Division/Ops/Branch and Account to build the matrix.  the measures are # of accounts (sum of accounts at each level), and then expiring rate, renewal rate, rate change from expiring and rate change %.  These are all measures that work at each level as seen in the screenshot.

What I want to do is create a new measure that will give me the number of accounts at each level who have a Rate Change % less then 5%., so for example in the screenshot above, the measure should have these values (sorry for the handwriting):

I tried to use the existing # of accounts measure and filter it for only accounts that have Rate Change % < 5, but isn't working.

Any assistance would be appreciated.

Scott

[tamerj1]

Hi @scabral 

please try

New =
SUMX (
SUMMARIZE (
'Table',
'Table'[Company],
'Table'[Division],
'Table'[Ops],
'Table'[Branch],
'Table'[Account]
),
IF ( [Rate Change %] < 0.05, 1, 0 )
)

you may also try

New =
SUMX (
VALUES ( 'Table'[Account] ),
IF ( [Rate Change %] < 0.05, 1, 0 )
)

[scabral]

Hi tamerj1,

thanks for the advice.  In the first query, if my account field belongs to another dimension table, how would i write the DAX?  So the Hierarchy fields Company/Division/Ops/Branch are in a dimension table called 'Host Office' and account is in another table called 'Client'.  I need to basically summarize columns from both those tables, but Summarize doesn't allow it.

[tamerj1]

@scabral 

You try summarizing the fact table 

New =
SUMX (
SUMMARIZE (
FactTable,
'Dim1'[Company],
'Dim1'[Division],
'Dim1'[Ops],
'Dim1'[Branch],
'Dim2'[Account]
),
IF ( [Rate Change %] < 0.05, 1, 0 )
)

[scabral]

sorry for all the questions, but I now get all the accounts with blanks for the other measures but also get the correct ones that have the measures like this:

[tamerj1]

@scabral 

New =
SUMX (

IF ( 

[# of Accts] <> BLANK ( ),
SUMMARIZE (
FactTable,
'Dim1'[Company],
'Dim1'[Division],
'Dim1'[Ops],
'Dim1'[Branch],
'Dim2'[Account]
),
IF ( [Rate Change %] < 0.05, 1, 0 )
)

)