Forum Discussion

strangerMike's avatar
strangerMike
Helper II
9 years ago

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)

 

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.  

 

Need Help

5 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    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
    • strangerMike's avatar
      strangerMike
      Helper II
      9 years ago

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

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        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. :smileyhappy:

         

        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