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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
strangerMike
Helper II
Helper II

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
Employee
Employee

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!

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

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

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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