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 dep...
Need Help
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

      • strangerMike's avatar
        strangerMike
        Helper II
        9 years ago

        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.