Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
wemsomba10
Frequent Visitor

Hello Developers

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
 
   
1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

 

View solution in original post

2 REPLIES 2
Kedar_Pande
Super User
Super User

@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

Anonymous
Not applicable

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.

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.