Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

dividing by column values

Hi, new to P_BI.

 

I have looked at several questions and solutions to this topic and googled as well, yet I cannot get my syntax correct.

 

Savings Pct = SUM('Results_OON-ERS_UHA_SIA-001279_SUMMARY'[BestSavings_ERSwhiteSpace])/SUM('Results_OON-ERS_UHA_SIA-001279_SUMMARY'[AllowedWithSavings_ERSwhiteSpace])

 

Savings Pct2 = DIVIDE('Results_OON-ERS_UHA_SIA-001279_SUMMARY'[BestSavings_ERSwhiteSpace],'Results_OON-ERS_UHA_SIA-001279_SUMMARY'[AllowedWithSavings_ERSwhiteSpace])

 

Savings Pct3 = DIVIDE(CALCULATE(SUM('Results_OON-ERS_UHA_SIA-001279_SUMMARY'[BestSavings_ERSwhiteSpace])),CALCULATE(SUM('Results_OON-ERS_UHA_SIA-001279_SUMMARY'[AllowedWithSavings_ERSwhiteSpace])),0)*100

 

Savings Pct4 = DIVIDE(SUM('Results_OON-ERS_UHA_SIA-001279_SUMMARY'[BestSavings_ERSwhiteSpace]),SUM('Results_OON-ERS_UHA_SIA-001279_SUMMARY'[AllowedWithSavings_ERSwhiteSpace]))),0)

 

BestSavingsSource ERSwhiteSpaceBestSavings ERSwhiteSpaceAllowedWithSavings ERSwhiteSpace
CI-Choice$20,872.60$66,184.92
CI-GoodHistory$516,915.85$3,400,632.41
NS-CDA$11,284.18$37,661.01
NS-Neg$264.65$546.04
NS-Supp$380,078.60$1,111,611.88

 

My savings pct's should be.

 

Savings Pct
31.54%
15.20%
29.96%
48.47%
34.19%
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi Anonymous ,

    Sorry for the delay.

    Actually, you'd better not share the Actual data in this community as data scecurity. 

    Your data sample is a little complex for me to understand. Could you only share the data sample which is related to your question.

     

    If you want to convert this

    "IF ATTR([BestSavingsSource ERSwhiteSpace])<>'ERS' then SUM([BestSavings ERSwhiteSpace])/SUM([AllowedWithSavings ERSwhiteSpace])

    ELSE SUM([BestSavings ERSwhiteSpace])/SUM([BilledWithSavings ERSwhiteSpace]) 

    "to measure in Power BI, you may could try the measure below.

    If the [BestSavingsSource ERSwhiteSpace] is a measure, you could try the formula below.

     

    =
IF (
    [BestSavingsSource ERSwhiteSpace]<> 'ERS',
    SUM ( [BestSavings ERSwhiteSpace] ) / SUM ( [AllowedWithSavings ERSwhiteSpace] ),
    SUM ( [BestSavings ERSwhiteSpace] ) / SUM ( [BilledWithSavings ERSwhiteSpace] )
)

    If  [BestSavingsSource ERSwhiteSpace] is a column, you could try this :

    =
IF (
  MAX([BestSavingsSource ERSwhiteSpace] )<> 'ERS',
    SUM ( [BestSavings ERSwhiteSpace] ) / SUM ( [AllowedWithSavings ERSwhiteSpace] ),
    SUM ( [BestSavings ERSwhiteSpace] ) / SUM ( [BilledWithSavings ERSwhiteSpace] )
)

    If you still need help, please share the most simple data sample.

    Best  Regards,

    Cherry

     

  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi Anonymous ,

    Have you solved your problem?

    If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

    If you still need help, please feel free to ask.

    Best  Regards,

    Cherry

     

9 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi Anonymous ,

     

    Please create the calculated column with the formula below.

     

    Column = 
    DIVIDE('Table1'[BestSavings ERSwhiteSpace],'Table1'[AllowedWithSavings ERSwhiteSpace])

    Here is your desired output.

     

     

    Best  Regards,

    Cherry

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hello Cherry and thank you for your support.

       

      In the interum I discovered that I was using the "New Column" selection and then discovered the "New Quick Measure" which led me to the same conclusion as you mention.

       

      I have tried the formula from the "New Quick Measure" in the "New Column" dialog and it does not seem to prodce the same results? However, I have bigger fish to fry!

       

      Be that as it may, I need to expand on my initiial problem (solved by you, thank you) and make this a bit more complicated and I hope you can help please.

       

      The actual table looks like this below:

       

      And the calculation for ERS is:

      [BestSavings ERSwhiteSpace]/[BilledWithSavings ERSwhiteSpace]

       

      While all the others are what we created yesterday:

      [BestSavings ERSwhiteSpace] / [AllowedWithSavings ERSwhiteSpace]

       

      I need the Measure to calculate the two differently yet creating only the one "New Column"

       

      In Tableau it looks like this: (I know why not just keep Tableau, too long a story, and we are moving to P-BI hence my need for early support, UGH!)

       

      IF ATTR([BestSavingsSource ERSwhiteSpace])<>'ERS' then SUM([BestSavings ERSwhiteSpace])/SUM([AllowedWithSavings ERSwhiteSpace])

      ELSE SUM([BestSavings ERSwhiteSpace])/SUM([BilledWithSavings ERSwhiteSpace]) END

       

      I do not see the capacity of "New Quick Measure" to accomplish this and I am concerend about my findings with "New Column."

       

      So with this new information can you help me further please?

       

      Thank you.

       

      WHP

       

      Row Labels  TotalBilled  BilledWithSavings_ERSwhiteSpace  AllowedWithSavings_ERSwhiteSpace  BestSavings_ERSwhiteSpaceGrand Total $  29,561,541 $                                             27,798,829 $                                                          5,661,985 $                                      7,006,137

      ERS in WhiteSpace
      Capture RateSavings Pct
      CI-Choice $        201,676 $                                                   201,676 $                                                                66,185 $                                            20,873100.0%31.5%
      CI-GoodHistory $    8,550,366 $                                               8,550,366 $                                                          3,400,632 $                                          516,916100.0%15.2%
      ERS $  15,173,384 $                                             15,173,384 $                                                          1,045,349 $                                      6,076,721100.0%40.0%
      NS-CDA $        137,190 $                                                   137,190 $                                                                37,661 $                                            11,284100.0%30.0%
      NS-Neg $            1,534 $                                                        1,534 $                                                                      546 $                                                  265100.0%48.5%
      NS-Supp $    3,734,679 $                                               3,734,679 $                                                          1,111,612 $                                          380,079100.0%34.2%
      - $    1,762,713 $                                                               -   $                                                                         -   $                                                      -  0.0%0.0%
      94.0%28.5%
      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi Anonymous ,

         

        I'm afraid that you may have confused about calcualted column and measure.

         

        If I understand your requirement correctly that you want to create a measure now?

         

        If it is convenient, could you re-upload your data sample with table format, it is a little difficult for us to identify your data model.

         

        I would also appreciate it if you could share your desired output.

         

        Best Regards,

        Cherry