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
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
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.
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:
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 )
)
)
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
15 | |
14 | |
12 | |
9 |
User | Count |
---|---|
58 | |
23 | |
22 | |
19 | |
13 |