filter and slicer

[Ritaf]

Hi i have an report with dates slicer from (date) to (date).

One of my vizualizations on this report is gauge shows toatal sales until last dates from early target.

The model have a targets table with monthly target, dimdatestable and sales table.

for getting an yearly target i use an dax measure:

yearly_target = CALCULATE(SUMX(Targests,'Targests'[Target $]),ALLEXCEPT(dimDate,'dimDate'[year]))

the result in totals isn't filtering an year, i resolved it by using an filter(by year) vizual , but i sure there is a dax solution to correct it. 

year	yearly_target	total sales
2019	3,280,575	377,025
Total	6,330,575	377,025

May you help me?

Thanks a lot, Rita

[v-xicai]

Hi  @Ritaf ,

Does that make sense? If so, kindly mark my answer as a solution to help others having the similar issue and close the case.

Best regards

Amy Cai

[Anonymous]

Hello Ritaf,

Is this OLTP and DW Modelling.

Regards

Rohit

[v-xicai]

Hi @Ritaf ,

This looks like a measure totals problem that total row data join in calculation. You can try to create new measure yearly_target_New like DAX below.

yearly_target_New=

VAR yearly_target = CALCULATE(SUMX(Targests,'Targests'[Target $]),ALLEXCEPT(dimDate,'dimDate'[year]))

VAR _table = SUMMARIZE('Targests',[year],"_Value",[yearly_target ])

RETURN

IF(HASONEVALUE([year]),[yearly_target],SUMX(_table,[_Value]))

You can refer to the similar case: https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907 .

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ritaf · Accepted solution]

Hi Amy,

sorry for no reaction, i was sick.

Unfortunately your solution is'nt working

i get very strange total that makes no sence...it's even greater than sum of all targets in a targets table.

I got a good solution from one of facebook groups:

yrealtarget = sumx(VALUES('dimDate'[year]),[yearly_target])

(yearly target is my origion formula :

yearly_target = CALCULATE(SUMX(Targests,'Targests'[Target $]),ALLEXCEPT(dimDate,'dimDate'[year])))

[v-xicai]

Hi  @Ritaf ,

Glad to hear the issue is resolved,  you can accept your reply as solution, that way, other community members would benefit from your solution.

Best regards

Amy Cai

[Ritaf]

Thanks i did it 🙂

[Greg_Deckler]

Hi Rita, this looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also, not sure if it helps but you might find my time intelligence stuff useful. 
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

If none of that works, will need more information like sample data, etc. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490