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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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