cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
strangerMike Regular Visitor
Regular Visitor

New Column calculation

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)

 

Capture1.PNG

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.  

 

5 REPLIES 5
Phil_Seamark Super Contributor
Super Contributor

Re: New Column calculation

Hi @strangerMike

 

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					

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

strangerMike Regular Visitor
Regular Visitor

Re: New Column calculation

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

 

Capture1.PNG

 

Capture2.PNG

 

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

v-ljerr-msft Super Contributor
Super Contributor

Re: New Column calculation

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. Smiley Happy

 

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

strangerMike Regular Visitor
Regular Visitor

Re: New Column calculation

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% ?

Capture1.PNGCapture2.PNG

Did I error?  Thank you.

Highlighted
v-ljerr-msft Super Contributor
Super Contributor

Re: New Column calculation

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. Smiley Happy

 

Regards

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 33 members 958 guests
Please welcome our newest community members: