Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Preparing for a certification exam? Ask exam experts all your questions on May 15th. Register now.

Reply
scabral
Helper IV
Helper IV

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

Hi,

 

i have the following matrix in Power BI Report:

Capture1.PNG

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

Capture1.PNG

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

5 REPLIES 5
tamerj1
Super User
Super User

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

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.

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

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:

Capture1.PNG

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

)

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Kudoed Authors