Hello Developers

[wemsomba10]

I have the below measure. it is returning the correct data, however i am still getting blank totals in any type of visual i use. Can someone help me here please.

Selected In Report = IF(ISBLANK(

 VAR Mth =

    CALCULATE (

    sum(My_spend_data[wins]),My_spend_data[Report Month Select Name]=SELECTEDVALUE('Selected Time Period'[Month Year]))

 VAR Qtr =

    CALCULATE (

    sum(My_spend_data[In Report]),My_spend_data[Report Quarter Select Name]=SELECTEDVALUE('Selected Time Period'[Quarter Year]))

VAR Yr =

    CALCULATE (

    sum(My_spend_data[In Report]),My_spend_data[Report Year]=SELECTEDVALUE('Selected Time Period'[Report Year]))

RETURN

IF(ISFILTERED('Selected Time Period'[Month Year]),Mth,

IF(ISFILTERED('Selected Time Period'[Quarter Year]),Qtr,

IF(ISFILTERED('Selected Time Period'[Report Year]),Yr))

)),0,

--else

[william1234]

Your measure works for rows but totals are blank because SELECTEDVALUE() returns blank in the total context.

Try this:

Selected In Report =
COALESCE(
    IF(
        ISFILTERED('Selected Time Period'[Month Year]),
        CALCULATE(
            SUM(My_spend_data[wins]),
            My_spend_data[Report Month Select Name] = SELECTEDVALUE('Selected Time Period'[Month Year])
        ),
        IF(
            ISFILTERED('Selected Time Period'[Quarter Year]),
            CALCULATE(
                SUM(My_spend_data[In Report]),
                My_spend_data[Report Quarter Select Name] = SELECTEDVALUE('Selected Time Period'[Quarter Year])
            ),
            CALCULATE(
                SUM(My_spend_data[In Report]),
                My_spend_data[Report Year] = SELECTEDVALUE('Selected Time Period'[Report Year])
            )
        )
    ),
    0
)

If totals are still blank, use a SUMX(VALUES(...), [Selected In Report]) measure for the visual total.

[Kedar_Pande]

@wemsomba10 

You need to ensure that your measure is explicitly handling totals by aggregating the results properly.
Corrected Measure:

Selected In Report = 
VAR Mth =
CALCULATE (
SUM(My_spend_data[wins]),
My_spend_data[Report Month Select Name] = SELECTEDVALUE('Selected Time Period'[Month Year])
)
VAR Qtr =
CALCULATE (
SUM(My_spend_data[In Report]),
My_spend_data[Report Quarter Select Name] = SELECTEDVALUE('Selected Time Period'[Quarter Year])
)
VAR Yr =
CALCULATE (
SUM(My_spend_data[In Report]),
My_spend_data[Report Year] = SELECTEDVALUE('Selected Time Period'[Report Year])
)
VAR Result =
IF(
ISFILTERED('Selected Time Period'[Month Year]), Mth,
IF(
ISFILTERED('Selected Time Period'[Quarter Year]), Qtr,
IF(ISFILTERED('Selected Time Period'[Report Year]), Yr, BLANK())
)
)
RETURN
IF(
NOT(ISINSCOPE('Selected Time Period'[Month Year])) &&
NOT(ISINSCOPE('Selected Time Period'[Quarter Year])) &&
NOT(ISINSCOPE('Selected Time Period'[Report Year])),
SUMX(
VALUES('Selected Time Period'[Month Year]), 
Result
),
Result
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Akshay_tri_123]

Hi everyone, I am new to Microsoft Fabric Community and looking forward to learning and connecting with others. Thank you!

[Anonymous · Accepted solution]

Hi @wemsomba10 ,

According to your code, I think your issue should be caused by IF() and ISFILTERED() function.

Selected In Report =
IF (
    ISBLANK (
        VAR Mth =
            CALCULATE (
                SUM ( My_spend_data[wins] ),
                My_spend_data[Report Month Select Name]
                    = SELECTEDVALUE ( 'Selected Time Period'[Month Year] )
            )
        VAR Qtr =
            CALCULATE (
                SUM ( My_spend_data[In Report] ),
                My_spend_data[Report Quarter Select Name]
                    = SELECTEDVALUE ( 'Selected Time Period'[Quarter Year] )
            )
        VAR Yr =
            CALCULATE (
                SUM ( My_spend_data[In Report] ),
                My_spend_data[Report Year]
                    = SELECTEDVALUE ( 'Selected Time Period'[Report Year] )
            )
        RETURN
            IF (
                ISFILTERED ( 'Selected Time Period'[Month Year] ),
                Mth,
                IF (
                    ISFILTERED ( 'Selected Time Period'[Quarter Year] ),
                    Qtr,
                    IF ( ISFILTERED ( 'Selected Time Period'[Report Year] ), Yr )
                )
            )
    ),
    0
)

There is no [Month Year]/[Quarter Year]/[Report Year] in subtotal, so it will return 0.

Here I suggest you to use SUMX() function to create a new measure based on [Selected in Report] measure.

If your visual is created by [Month Year]/[Quarter Year]/[Report Year] columns and [Selected in Report] measure, I suggest you to create a virtual table by SUMMARIZE().

Selected In Report (New) =
SUMX (
    SUMMARIZE (
        'Selected Time Period',
        [Report Year],
        [Quarter Year],
        [Month Year]
    ),
    [Selected in Report]
)

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.