topic Re: measure to count number of accounts that meet criteria within a matrix in DAX Commands and Tips

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

scabral — Fri, 14 Apr 2023 15:38:29 GMT

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

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

tamerj1 — Fri, 14 Apr 2023 15:57:46 GMT

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

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

scabral — Fri, 14 Apr 2023 17:30:35 GMT

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.

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

tamerj1 — Fri, 14 Apr 2023 18:04:15 GMT

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

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

scabral — Fri, 14 Apr 2023 18:22:13 GMT

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:

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

tamerj1 — Fri, 14 Apr 2023 19:38:24 GMT

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

)