summarize by site did not return correct results

[Anonymous]

With Reference to the thread here ,I am expainging another issue I am facing currently and unable to get it to fix inspite of including Site[SiteID] in the summarize().

https://community.powerbi.com/t5/Developer/dax-measure-to-calculate-number-of-tickets-open-at-EOM-wi...

My measure:

CALCULATE(sumx(ADDCOLUMNS(SUMMARIZE(Int_New, 'Date'[Year], 'Date'[Month]), "MonthlyTotal", CALCULATE(DISTINCTCOUNT(Internal[ID])) - CALCULATE(DISTINCTCOUNT(Internal[ID]), USERELATIONSHIP('Date'[Date], Int_New[CloseDate]))), [MonthlyTotal]), FILTER(ALLselected('Date'[Date]), 'Date'[Date]<=EOMONTH(max('Internal'[OpenDate]), 0)))

Folliwing the above,i could obtain the correct aggregated values for per month and year,but when i bring in Site column from Site dimension(like below),i get values.

CALCULATE(sumx(ADDCOLUMNS(SUMMARIZE(Int_New, 'Date'[Year], 'Date'[Month],Sites[SiteID]), "MonthlyTotal", CALCULATE(DISTINCTCOUNT(Internal[ID])) - CALCULATE(DISTINCTCOUNT(Internal[ID]), USERELATIONSHIP('Date'[Date], Int_New[CloseDate]))), [MonthlyTotal]), FILTER(ALLselected('Date'[Date]), 'Date'[Date]<=EOMONTH(max('Internal'[OpenDate]), 0)))

I am not sure why this is happening and would like to slice by Site.

Below is the sample Internal fact table and Site dimension table.

 Slicing by SiteID giving wrong results-

                                       Also,when i bring in SiteID in the summarize(),I get wrong OpenFinal like below:

                                                      Following is the correct output:

[Anonymous · Accepted solution]

Thanks Dale,the solution to it is as follows:

Create 3 measure as below-

• Measure1:  CALCULATE(DISTINCTCOUNT(Internal[Id]), FILTER(all('Date'[Date]), 'Date'[Date]<=max('Date'[Date])))
• Measure2:      VAR maxDate =
      MAX ( 'Date'[Date] ) + 1
  RETURN
      CALCULATE (
          DISTINCTCOUNT ( Internal[Id] ),
          FILTER (
              ALL ( 'Date'[Date] ),
              'Date'[Date] <= maxDate
                  && 'Date'[Date] <> BLANK ()
          ),
          USERELATIONSHIP ( 'Date'[Date], Internal[CloseDate] )
      ) 
• Measure3: [Measure1]-[Measure2]

[v-jiascu-msft]

Hi @Anonymous,

Try this formula, please.

Measure 3 =
CALCULATE (
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE ( Internal, 'Date'[Date].[Year], 'Date'[Date].[Month] ),
            "MonthlyTotal", CALCULATE ( DISTINCTCOUNT ( Internal[ID] ) )
                - CALCULATE (
                    DISTINCTCOUNT ( Internal[ID] ),
                    USERELATIONSHIP ( 'Date'[Date], Internal[CloseDate] )
                )
        ),
        [MonthlyTotal]
    ),
    FILTER (
        ALLSELECTED ( 'Date' ),
        'Date'[Date] <= EOMONTH ( MAX ( 'Internal'[OpenDate] ), 0 )
    )
)

Best Regards,

Dale

[Anonymous]

Dale,

I am using exactly the same formula as below but somehow the values grouped by Sites filtered are not giving correct numbers.

For excample,for Jan 2017 ,total open incidents are 11 but the graph and table just talk about 3 open with Site 2.What about the rest of the incidents.Below is the problem:

We need to first group the incidents by Year and Month ,and then group meausre by Site.I am using the same calculation as yours:

Open_final = CALCULATE(sumx(ADDCOLUMNS(SUMMARIZE(Internal,'Date'[Year],'Date'[Month]),"MonthlyTotal", CALCULATE(DISTINCTCOUNT(Internal[ID])) - CALCULATE(DISTINCTCOUNT(Internal[ID]), USERELATIONSHIP('Date'[Date], Internal[CloseDate]))),[MonthlyTotal]),FILTER(ALLselected('Date'[Date]), 'Date'[Date]<=EOMONTH(max('Internal'[OpenDate]), 0)))

In my real data,I see the following issue and it is wrong because the total for Jan 2018 says 49(correct-5+18+26) but when you group by and drag the Site,the numbers for detail levels are not correct.

In short,pls help me replicate the same functionality available with the following custom table in tabular model without having to create calculated table and suing available date/Location dim-

End of Month =

SELECTCOLUMNS(

FILTER(

    CROSSJOIN(

        ADDCOLUMNS(

        FILTER(

            CALENDAR("2017-01-01",TODAY()),

            day([Date])=1

            ),

            "Reporting Month",var d = [Date]-1 RETURN DATE(YEAR(d),MONTH(d),1)

            ),

        'Table'),

        [Date] > [OpenedDate] &&

        [Date] < IF([CloseDate] = BLANK(),"2099-01-01",[CloseDate])

        ) ,

        "Month" , [Month] ,

        "ID" , [IncidentId],

        "Site", [SiteID]

)

[v-jiascu-msft]

Hi @Anonymous,

Based your sample data, it's 2 actually. 

The formula isn't the one I posted.

Open_final =
CALCULATE (
    SUMX (
        ADDCOLUMNS (
            SUMMARIZE ( Internal, 'Date'[Year], 'Date'[Month] ),
            "MonthlyTotal", CALCULATE ( DISTINCTCOUNT ( Internal[ID] ) )
                - CALCULATE (
                    DISTINCTCOUNT ( Internal[ID] ),
                    USERELATIONSHIP ( 'Date'[Date], Internal[CloseDate] )
                )
        ),
        [MonthlyTotal]
    ),
    FILTER (
        ALLSELECTED ( 'Date'[Date] ),
        'Date'[Date] <= EOMONTH ( MAX ( 'Internal'[OpenDate] ), 0 )
    )
)

Please check out and try again.

Best Regards,

Dale

[Anonymous]

Dale,

I tried your new formula too,but the issue is not by applying Site filter.It comes down to when i just darg in SiteID into the table,why doesnt it show all 11 IDs for 2017 Jan.Why does it just show Jan 2017,Site 2 like below:-

[v-jiascu-msft]

Hi @Anonymous,

It seems I have made things complicated. Try this formula, please.

Open_final =
VAR minDate =
    CALCULATE ( MIN ( Internal[OpenDate] ), ALL ( Internal ) )
RETURN
    CALCULATE (
        SUMX ( Internal, CALCULATE ( DISTINCTCOUNT ( Internal[ID] ) ) )
            - CALCULATE (
                SUMX ( Internal, CALCULATE ( DISTINCTCOUNT ( Internal[ID] ) ) ),
                USERELATIONSHIP ( 'Date'[Date], Internal[CloseDate] )
            ),
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date] <= MAX ( 'Date'[Date] )
                && 'Date'[Date] >= minDate
        )
    )

Best Regards,

Dale

[Ashish_Mathur]

Hi,

What is the difference between ID and Site ID.  Please describe the business question and your data.  Also, show the expected result.  Share data in a format that can be pasted in an Excel file.  

[Anonymous]

Ashish,'

ID is incident# and SiteID is site ID.Samples and expected results are already posted.