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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.