Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello
I am trying to capture some information from a Matrix.
I can successfully create a new column for one of the figures i need, but when I try and create another one i get the circular dependency error and I don't know what to do or how to correct it:
First new column:
CurBounce = IF(View_EmailMetricsUnionNatural[Metric Name] = "# of bounces",View_EmailMetricsUnionNatural[Cur Mo Cur Yr],0)
With this formula i can successfully grab the Cur Mo Cu Yr value in this example the number 3. I am displaying the column so i can see it worked.
Now if I try and create another New Column....using the same criteria, but trying to grab 2181 from '# emails sent', I get the Dependency error. Same code just a different Metric Name.
CurEmailsSent = IF(View_EmailMetricsUnionNatural[Metric Name] = "# emails sent",View_EmailMetricsUnionNatural[Cur Mo Cur Yr],0)
I don't know if it is worth mentioning, but if I delete the first new column (CrBounce) and create the second new column frist, then it creates the '# emails sent' successfully and i get the error when trying to create the second new column for CurBounce.
Esentially i need think of a way where i can make this calculation: 3 / 2181 *100 to get a percentage that I can display perhaps in a Card visual. Or "# of bounces" / "# emails sent" * 100
I have tried doing this in a New Measure but have not been successful with the syntax.
I am not even sure if what I want to do should be a new measure or a new column?
Unfortunately i don't have a lot of experience with power bi (yet). So if you can include examples that would be helpful.
Thanks for your time.
I think I understood your query. Please have a try with this calculated column and let me know how you get on
Bounce Rate = DIVIDE( CALCULATE( SUM('View_EmailMetricsUnionNatural'[Cur Mo Cur Yr]), FILTER( ALLSELECTED('View_EmailMetricsUnionNatural'), 'View_EmailMetricsUnionNatural'[Metric Name]="# of Bounces") ), CALCULATE( SUM('View_EmailMetricsUnionNatural'[Cur Mo Cur Yr]), FILTER( ALLSELECTED('View_EmailMetricsUnionNatural'), 'View_EmailMetricsUnionNatural'[Metric Name]="# emails Sent") ) ,0 ) * 100
Hi Phil, Thanks very much for your response.
So I tried the calculation but received the error:
"Column 'Cur Mo Cur Yr' in table View_EmailMetricsUnionNatural cannot be found or may not be used in this expression.
I created Cur Mo Cur Yr this way: Cur Mo Cur Yr = sum(View_EmailMetricsUnionNatural[Metric])
I think you are on to something.
I guess a big problem is trying to calculate the percent. It can't be a sum or averaged because it is a percent.
The information I need is there, but how to get it!? Thank you
PS also where would you display this new column? In a card or can it be displayed with the two other percent calculations in table (once working that is)?
Hi @strangerMike,
If I understand you correctly, you should be able to use the formula below to create a new measure, then show it on a Card visual.
Bounce Rate = DIVIDE ( CALCULATE ( [Cur Mo Cur Yr], FILTER ( 'View_EmailMetricsUnionNatural', 'View_EmailMetricsUnionNatural'[Metric Name] = "# of Bounces" ) ), CALCULATE ( [Cur Mo Cur Yr], FILTER ( 'View_EmailMetricsUnionNatural', 'View_EmailMetricsUnionNatural'[Metric Name] = "# emails Sent" ) ), 0 ) * 100
Regards
Sorry for my response delay, I was pulled away from this for a couple weeks!
At last attempt I created a new calculated measure using this suggestion:
Bounce Rate =
DIVIDE (
CALCULATE (
[Cur Mo Cur Yr],
FILTER (
'View_EmailMetricsUnionNatural',
'View_EmailMetricsUnionNatural'[Metric Name] = "# of Bounces"
)
),
CALCULATE (
[Cur Mo Cur Yr],
FILTER (
'View_EmailMetricsUnionNatural',
'View_EmailMetricsUnionNatural'[Metric Name] = "# emails Sent"
)
),
0
)
* 100
The result of this was put in a Card. But it looks like the amount is incorrect.
Below is a portion of matrix that has the two fields. And also the result Card. However I would expect the Bounce Rate to be .19% ?
Did I error? Thank you.
Hi @strangerMike,
A little weird! Could you share a sample pbix file which can reproduce the issue? So that we can help further investigate on it. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Regards
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |