Frequent Visitor

1 year ago

Solved

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

Anonymous

1 year ago

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.

4 Replies

Anonymous

Not applicable

1 year ago

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.

Kedar_Pande

Super User

1 year ago

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
New Member
1 month ago
Hi everyone, I am new to Microsoft Fabric Community and looking forward to learning and connecting with others. Thank you!

william1234

Helper II

1 month ago

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.

Forum Discussion

Hello Developers

4 Replies

Related content

custom visual development

Dynamic Measure developement

Develop Power Query outside Power BI or Excel

Power BI Development and Best Practices

Laptop Hardware Requirements for Power BI Desktop Developers