Power BI measure not showing total

[priya_rajendran]

Hi, I have created the below measure and it does not show the total value after applying the 'IF' filter. What should I do get the totals populated ? Since the total is not being populated, while i drill down on the visual, all the values are reflecting as blank.

Open_reqs =

var start_ = STARTOFMONTH(calendar_dim[cal_dt])

var end_ = ENDOFMONTH(calendar_dim[cal_dt])

var thisday = TODAY()

var openreq = IF(start_ >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1)-30, CALCULATE(COUNTROWS(Expected_joiners),allexcept(Expected_joiners,'CC Details'[Tech Org 1],'CC Details'[IDC CC]), Expected_joiners[Req Approved On]<= end_,Expected_joiners[Candidate Joining Date] = BLANK()))

return openreq

[tamerj1 · Accepted solution]

@priya_rajendran 
Please use the correct column names inside SUMMARIZE 

Open_reqs =
SUMX (
    SUMMARIZE ( calendar_dim, calendar_dim[Year], calendar_dim[Month] ),
    CALCULATE (
        VAR start_ =
            STARTOFMONTH ( calendar_dim[cal_dt] )
        VAR end_ =
            ENDOFMONTH ( calendar_dim[cal_dt] )
        VAR thisday =
            TODAY ()
        VAR openreq =
            IF (
                start_
                    >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ) - 30,
                CALCULATE (
                    COUNTROWS ( Expected_joiners ),
                    ALLEXCEPT ( Expected_joiners, 'CC Details'[Tech Org 1], 'CC Details'[IDC CC] ),
                    Expected_joiners[Req Approved On] <= end_,
                    Expected_joiners[Candidate Joining Date] = BLANK ()
                )
            )
        RETURN
            openreq
    )
)

[priya_rajendran]

Hi @tamerj1 , Thanks for your response! Your Dax returned the total and it works. My only concern is that I dont want the total of the values here. I would still want the count of rows in the total too as my earlier dax did. would it be possbile to get the same number 1795 instead of the sum 12565 ?

@tamerj1 , Slicing by month alone also returns the same result.

[tamerj1]

Hi @priya_rajendran 
Now the slicing by has changed from Year/Month hierarchy to month only. 
Please use

Open_reqs =
SUMX (
    VALUES ( calendar_dim[Month] ),
    CALCULATE (
        VAR start_ =
            STARTOFMONTH ( calendar_dim[cal_dt] )
        VAR end_ =
            ENDOFMONTH ( calendar_dim[cal_dt] )
        VAR thisday =
            TODAY ()
        VAR openreq =
            IF (
                start_
                    >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ) - 30,
                CALCULATE (
                    COUNTROWS ( Expected_joiners ),
                    ALLEXCEPT ( Expected_joiners, 'CC Details'[Tech Org 1], 'CC Details'[IDC CC] ),
                    Expected_joiners[Req Approved On] <= end_,
                    Expected_joiners[Candidate Joining Date] = BLANK ()
                )
            )
        RETURN
            openreq
    )
)

Also please copy paste the code that your are using in your file so I can check it

[priya_rajendran]

Hi, The slicing by month or year & month, both returns the same value

Open_reqs =

SUMX (

SUMMARIZE (calendar_dim, calendar_dim[cal_mth_nbr]),

CALCULATE (

VAR start_ =

STARTOFMONTH ( calendar_dim[cal_dt] )

VAR end_ =

ENDOFMONTH ( calendar_dim[cal_dt] )

VAR thisday =

TODAY ()

VAR openreq =

IF (

start_

>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ) - 30,

CALCULATE (

COUNTROWS ( Expected_joiners ),

ALLEXCEPT ( Expected_joiners, 'CC Details'[Tech Org 1], 'CC Details'[IDC CC] ),

Expected_joiners[Req Approved On] <= end_,

Expected_joiners[Candidate Joining Date] = BLANK ()

)

)

RETURN

openreq

)

)

[tamerj1]

Hi @priya_rajendran 
Please try

Open_reqs =
VAR start_ =
    MIN ( calendar_dim[cal_dt] )
VAR end_ =
    MAX ( calendar_dim[cal_dt] )
VAR thisday =
    TODAY ()
VAR openreq =
    IF (
        start_
            >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ) - 30,
        CALCULATE (
            COUNTROWS ( Expected_joiners ),
            ALLEXCEPT ( Expected_joiners, 'CC Details'[Tech Org 1], 'CC Details'[IDC CC] ),
            Expected_joiners[Req Approved On] <= end_,
            Expected_joiners[Candidate Joining Date] = BLANK ()
        )
    )
RETURN
    openreq

[priya_rajendran]

Hi @tamerj1  This does not give the totals value. This works same as my 1st dax. Your summarize dax actually works perfectly fine for sum, but only for the measures where i am looking for the count aggregate in total, I am stuck.

Appreciate your help!

[tamerj1]

@priya_rajendran 

Can we connect via zoom or teams? If so please share a meeting link in a private message. Thank you

[tamerj1]

Hi @priya_rajendran 
What are you slicing by in your report?