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

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

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

       

      Regards