Forum Discussion

wemsomba10's avatar
wemsomba10
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(...
  • Anonymous's avatar
    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.

     

william1234's avatar
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.