dividing by column values

[Anonymous]

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 ERSwhiteSpace	BestSavings ERSwhiteSpace	AllowedWithSavings 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%

[v-piga-msft · Accepted solution]

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]

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]

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 Rate	Savings Pct
CI-Choice	$        201,676	$                                                   201,676	$                                                                66,185	$                                            20,873	100.0%	31.5%
CI-GoodHistory	$    8,550,366	$                                               8,550,366	$                                                          3,400,632	$                                          516,916	100.0%	15.2%
ERS	$  15,173,384	$                                             15,173,384	$                                                          1,045,349	$                                      6,076,721	100.0%	40.0%
NS-CDA	$        137,190	$                                                   137,190	$                                                                37,661	$                                            11,284	100.0%	30.0%
NS-Neg	$            1,534	$                                                        1,534	$                                                                      546	$                                                  265	100.0%	48.5%
NS-Supp	$    3,734,679	$                                               3,734,679	$                                                          1,111,612	$                                          380,079	100.0%	34.2%
-	$    1,762,713	$                                                               -	$                                                                         -	$                                                      -	0.0%	0.0%
94.0%	28.5%

[v-piga-msft]

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

[Anonymous]

Aha! I think I have figured out how to share the example with you Cherry.

https://drive.google.com/drive/folders/1BZnRo5mvvPIvmkDeJaVzNx6yHmM3Lq1w?usp=sharing

Please let me know if this is the correct process for PowerBI community uploading and sharing examples.

Thank you for your patience and support!

WHP

[Anonymous]

Good morning Cherry, I have no idea how to upload a data sample?

[Anonymous]

Good morning Cherry. I remain stimied on this issue and I have place sample data as requested on Google My Drive.

Would appreciate your's or any other BI_P Power User help in resolving this please.

Thank you.

WHP

[v-piga-msft · Accepted solution]

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

[Anonymous]

Hi Cherry, thank you, I will try your suggestions.

Cheers

WHP

[v-piga-msft · Accepted solution]

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