Forum Discussion

Shaik_2002

Frequent Visitor

2 years ago

Solved

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 Se...

Need Help

Shaik_2002
2 years ago
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.

Shaik_2002

2 years ago

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,

https://www.dropbox.com/scl/fi/gdte9oe6lcd5sgs2lc5iv/Sample-Power-Bi-Data.pbix?rlkey=9ltgzdxbdy9074kw5ar8z8c1i&st=tqjes2fd&dl=0

Thank you all.

Anonymous

Not applicable

2 years ago

Hi 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 Zhou

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

Shaik_2002

Frequent Visitor

2 years ago

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.

https://www.dropbox.com/scl/fi/gdte9oe6lcd5sgs2lc5iv/Sample-Power-Bi-Data.pbix?rlkey=9ltgzdxbdy9074kw5ar8z8c1i&st=nyu8x4yk&dl=0

https://www.dropbox.com/scl/fi/b66kr2jby2di4rty67pyh/Sample-Data.xlsx?rlkey=ixaokfwbxyl9u7yaeckjpn5nw&st=hxv3lxvd&dl=0