Forum Discussion
Values not getting added in TOTALS
Hi All,
Here I Have some measures,
On-Date Target =
VAR Key = AVERAGE('Table'[Value])
RETURN
IF(HASONEVALUE('Table'[Cities]),Key,SUMX(VALUES('Table'[Cities]),Key))
Day Target =
VAR SelectedDate = SELECTEDVALUE('DateTable'[Date])
VAR SelectedMonth = MONTH(SelectedDate)
VAR SelectedYear = YEAR(SelectedDate)
RETURN
CALCULATE(
([On-Date Target]),
'DateTable'[Date] <= SelectedDate,
'DateTable'[Date] >= DATE(SelectedYear, SelectedMonth, 1) -- MTD
)
And I'm getting like this.
while using Day Target Measure, I'm getting blank for Kannur. So I used another Measure Day_Target variable,
Day_Target =
VAR SelectedDate = SELECTEDVALUE('DateTable'[Date])
VAR SelectedMonth = MONTH(SelectedDate)
VAR SelectedYear = YEAR(SelectedDate)
RETURN
IF(ISBLANK([On-Date Target]),
CALCULATE(
[On-Date Target],
'DateTable'[Date] <= SelectedDate,
'DateTable'[Date] >= DATE(SelectedYear, 1, 1) -- YTD
),
CALCULATE(
[On-Date Target],
'DateTable'[Date] <= SelectedDate,
'DateTable'[Date] >= DATE(SelectedYear, SelectedMonth, 1) -- MTD
)
)
now I'm getting value for Kannur, but it is not adding in TOTALS.
Please suggest any solution to this.
Thanks in advance!!
Hi All,
Issue is resolved by using this below measure
Total_MTD_YTD =IF(ISINSCOPE('Table'[Countries]),[Day_Target],CALCULATE(SUMX(ALL('Table'[Countries]),[Day_Target])))Thank you all.Hi Anonymous ,
In the Sample file which you published, the data is getting like this.
But I need like this
By using first two measures, I didn't get the expected result.
Day.Target = VAR SelectedDate = SELECTEDVALUE(DateTable[Date]) VAR SelectEdMonth = MONTH(SelectedDate) VAR SelectedYear = YEAR(SelectedDate) RETURN CALCULATE( ([On-Date Target]), 'DateTable'[Date] <= SelectedDate, 'DateTable'[Date] >= DATE(SelectedYear, SelectedMonth, 1) -- MTD )Day Target = VAR SelectedDate = MAX('DateTable'[Date]) VAR SelectedMonth = MONTH(SelectedDate) VAR SelectedYear = YEAR(SelectedDate) VAR MTD_Value = CALCULATE( [On-Date Target], DATESBETWEEN( 'DateTable'[Date], DATE(SelectedYear, SelectedMonth, 1), SelectedDate ) ) VAR YTD_Value = CALCULATE( [On-Date Target], DATESBETWEEN( 'DateTable'[Date], DATE(SelectedYear, 1, 1), SelectedDate ) )
But using third measure, I got the expected result.Day_Target = IF( ISINSCOPE('Table'[Countries]), [Day Target], CALCULATE( SUMX( ALL('Table'[Countries]), [Day Target] ) ) )I'm sharing my sample file,
Thank you all.
7 Replies
Shaik_2002 , You need a measure like
Day_Target =
Sumx( Values(Geography[Cities]) ,
VAR SelectedDate = SELECTEDVALUE('DateTable'[Date])
VAR SelectedMonth = MONTH(SelectedDate)
VAR SelectedYear = YEAR(SelectedDate)
RETURN
IF(ISBLANK([On-Date Target]),
CALCULATE(
[On-Date Target],
'DateTable'[Date] <= SelectedDate,
'DateTable'[Date] >= DATE(SelectedYear, 1, 1) -- YTD
),
CALCULATE(
[On-Date Target],
'DateTable'[Date] <= SelectedDate,
'DateTable'[Date] >= DATE(SelectedYear, SelectedMonth, 1) -- MTD
)
) )
You can summarize if there more than one group by in visual
Shaik_2002 First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
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: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8Hi Shaik_2002 ,
As far as I know, your issue should be caused by IF() function.
Here I have two workarounds:
1. You can sum [Day_Target] to get correct total directly.New_Day_Target = SUMX ( VALUES ( 'Table'[Citys] ), [Day_Target] )2. You can create a virtual table in your calculation.
New_Day_Target = VAR _Virtual = SUMMARIZE ( 'Table', 'Table'[Cities], "Day_Target", VAR SelectedDate = SELECTEDVALUE ( 'DateTable'[Date] ) VAR SelectedMonth = MONTH ( SelectedDate ) VAR SelectedYear = YEAR ( SelectedDate ) RETURN IF ( ISBLANK ( [On-Date Target] ), CALCULATE ( [On-Date Target], 'DateTable'[Date] <= SelectedDate, 'DateTable'[Date] >= DATE ( SelectedYear, 1, 1 ) ), CALCULATE ( [On-Date Target], 'DateTable'[Date] <= SelectedDate, 'DateTable'[Date] >= DATE ( SelectedYear, SelectedMonth, 1 ) ) ) ) RETURN SUMX ( _Virtual, [Day_Target] )If this reply still couldn't help you solve your issue please share a sample file with us. This will make it easier for us to find the solution.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi All,
Issue is resolved by using this below measure
Total_MTD_YTD =IF(ISINSCOPE('Table'[Countries]),[Day_Target],CALCULATE(SUMX(ALL('Table'[Countries]),[Day_Target])))Thank you all.Hi Shaik_2002 ,
I am glad that you could find the solution by yourself.
Here I update the measure, you can achieve your goal by only two measures.
Day Target = VAR SelectedDate = SELECTEDVALUE ( 'DateTable'[Date] ) VAR SelectedMonth = MONTH ( SelectedDate ) VAR SelectedYear = YEAR ( SelectedDate ) VAR _AVG = AVERAGE ( 'Table'[Value] ) VAR _MonthLevel = CALCULATE ( AVERAGE ( 'Table'[Value] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Countries] ), 'Table'[Billing Date] <= SelectedDate && 'Table'[Billing Date] >= DATE ( SelectedYear, SelectedMonth, 1 ) ) -- MTD ) VAR _YearLevel = CALCULATE ( AVERAGE ( 'Table'[Value] ), FILTER ( ALLEXCEPT ( 'Table', 'Table'[Countries] ), 'Table'[Billing Date] <= SelectedDate && 'Table'[Billing Date] >= DATE ( SelectedYear, 1, 1 ) ) -- YTD ) RETURN IF ( ISBLANK ( _AVG ), IF ( ISBLANK ( _MonthLevel ), _YearLevel, _MonthLevel ), _AVG )Measure = IF(HASONEVALUE('Table'[Countries]),[Day Target],SUMX(ALLSELECTED('Table'[Countries]),[Day Target]))Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous,
Sorry for the delay, I have tried the formula which you have given and still it is not working.
I'm attaching the sample pbix file along with the sample excel data.
Thanks in Advance!!
