Multiply colummns and got wrong answer. Any suggestions?

[chaichatp]

[amitchandak]

Can you share sample data and sample output and formulas. If possible please share a sample pbix file after removing sensitive information.Thanks.
Proud to be a Datanaut My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[chaichatp]

Here is Data sample and The SCORE Allct*3 Yr column got an issue now. 

Since i would like to be able to filter the date by using the silcer and want the measure to recalculate the % of total row all the time so the formula in measure is 

SCORE Allct = DIVIDE(SUM('TOP500-CORREL0_7_TEST'[SCORE]),CALCULATE(SUM('TOP500-CORREL0_7_TEST'[SCORE]),ALLSELECTED('TOP500-CORREL0_7_TEST'[Ticker])),0)

and this is formula for SCORE Allct*3 Yr

SCORE Allct*3Yr = ([SCORE Allct]*'TOP500-CORREL0_7_TEST'[3 Yr. Annl TTR])/100

Ticker	Date	Score(RAW DATA)	SCORE Allct (Measure)	3 Yr Annl TTR(RAW DATA)	ScoreAllct * 3 Yr
A	2/20/2020	8	17.39%	33%	5.74%
B	2/20/2020	7	15.22%	20%	3.04%
C	2/20/2020	6	13.04%	15%	1.96%
D	2/20/2020	8	17.39%	15%	2.61%
E	2/20/2020	9	19.57%	10%	1.96%
F	2/20/2020	8	17.39%	5%	0.87%

[Greg_Deckler]

What is the formula for the measure in your column formula? I'm guessing it has something to do with row versus filter context.

[chaichatp]

I should say like this, it seems it didnt multiply with my SCORE Allct at all(Measure), you can compare both yellow columns, it just divided 3 Yr. Annl TTR by 100 and here is the formula for both columns. 

SCORE Allct = DIVIDE(SUM('TOP500-CORREL0_7_TEST'[SCORE]),CALCULATE(SUM('TOP500-CORREL0_7_TEST'[SCORE]),ALLSELECTED('TOP500-CORREL0_7_TEST'[Ticker])),0)

and the output column is

SCORE Allct*3Yr = ([SCORE Allct]*'TOP500-CORREL0_7_TEST'[3 Yr. Annl TTR])/100

[Greg_Deckler]

But Score Allct is a measure, it is going to calculate differently in row context versus in your visual. Because your Score Allct 3 Year is a calculated column, I believe that is where your issue lies. So, you likely either need to switch Score Allct 3 Year to be a measure or change Score Allct to a column. One way or the other.

[chaichatp]

Which way is simpler?

[Greg_Deckler · Accepted solution]

Probably simpler to convert your 3 year to a measure since you are already getting what you expect for your Allct Score measure in you visualization.

[chaichatp]

Already solved. Great

I have more question for the same table for Total row since the number should not be the same as the one before I multiply with SCORE Allct, they should be much less than the 2 colmns before. Any thought on that?

[Greg_Deckler]

Right, thay is the measure totals problem. Look in the Quick Measure gallery for my quick measure, Measure Totals, The Final Word.

[chaichatp]

Will look into it. THanks