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.

     

Kedar_Pande's avatar
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's avatar
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!