Sum is not working on table Visual After using Measure with Text

[Chanleakna123]

hi all , 

Both Total NSR Weekly  and Ach/Not Ach are in the same number , which is totally Wrong ,  While i put some formula : 

Ach/NotAch Status = SWITCH(TRUE(),[Ach status]="Ach",[Total NSR Weekly],0)

and Incentive Value , the Sum is not correct  i think this is coming from Incentive Value as well. 

Incentive value is ( another separated table ) . , the result should be 17,554.27

[sevenhills]

Ach/NotAch Status = SUMX( 'Table 1', IF('Table 1'[Ach status]="Ach",'Table 1'[Total NSR Weekly],0))

Try this ... 

[Chanleakna123]

Hi @sevenhills  , Ach status is a measure , so using with tables , not sure which one exactly. 
Ach status calculated from other field as well. 

all are in measure 

[sevenhills]

Hi

For the total row, your statement is picking the first (alphabetical) value, [Ach status]="Ach", and using all rows and doing total. I don't know the table name used 'Table 1'. I took your formula and adjusted.

Since I see you are advanced user based on the profile, 

a) I suggest to use SUMX (for Totals and for every row) and IF conditions (for every row). Tune your formula accordingly.

b) If the above method does not work, change your formula to use CALCULATE, SUM, FILTER ... combination

https://community.powerbi.com/t5/Desktop/Search-in-text-and-sum-values-measure/m-p/627058 

Hope this helps

[Chanleakna123]

hi , @sevenhills  @Pragati11  , 

now i am really getting lost . can you all help me out on this. 

i have Table of Target , Table of NSR  and table of Incentive Value = 0.1$ 

the formula : 

1. %Weekly Result = IFERROR([Total NSR Weekly]/[Total Target Weekly],0)

2. Ach/NotAch Status = SUMX(VALUES('445 Calendar'[Week Number]), IF('Target Weekly Stability'[%Weekly Result]>=0.979,[Total NSR Weekly],0))

3.Testing = SUMX('Weekly Stability Pack',[Ach/NotAch Status])

4. incentive value = [Testing]*'Weekly Stability Incentive Value'[Total Weekly Incentive Value]

The Right Result on incentive value = 14043.12+3511.15=17554.27

[Greg_Deckler]

@Chanleakna123 - Can you provide sample data and the measure formulas that you are using? The problem is that your measures are executing in the context of ALL in the totals rows. See if this helps:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907#M23...

[Chanleakna123]

hi @Greg_Deckler , 

i have put the file in dropbox , those are the sources i connect and in relationship together in PBI ,i'm so confused with the incentive value , seems like i never get it right. 

though my purpose is : if NSR/Target >=0.979,Ach,NotAch , 

if the customers acheive , need to calculation All NSR in CAN or PET Sparkling 1.5 or PET Sparkling 2.25 (Baed on Ach )* Incentive Value 

every incentive calculation based on Period as i mentioned . 

https://www.dropbox.com/scl/fi/3637poyomfj093qj7a2lo/Incentive-Value.xlsx?dl=0&rlkey=yi4244xzn2l0j46...

[Icey]

Hi @Chanleakna123 ,

What are the relationships among the tables? Please create a .pbix file, with relationships and measures, based on the .xlsx file you shared above.

Best Regards,

Icey

[Pragati11]

Hi @Chanleakna123 ,

Refer the brilliant blog from @Greg_Deckler on fixing the totals when they are not right:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Thanks,

Pragati

[Chanleakna123]

hi @Pragati11  , i'm sorry ,when i apply , seems i am getting lost , not able to know which field i should use to make it right , 

can you help me on this to refer to what i post ?