Grand total not showing the correct value when using SUMX and Countrows

[SimonGoldPPG]

I'm having trouble getting the correct value in the Grand Total using the below Measure. 

Facility Active Days:=
SUMX('Facility Region', COUNTROWS(DISTINCT('Global Daily Census'[CensusDate])))

Facility Region is a dimension table with a one to many relationship to Global Daily Census. 

I expect the measure to itirate the Faciility Region table, and count the distinct Date values for each facility and sum up the results. 

the results display correctly when using the facilitiy names in the rows and each facility displayes the correct ammount of Unique date, However the grand total simply displays a multiplication of the total rows in the facility region table, times the largest unique count of the date column. 

My model is built in SSAS tabular and the below is an excel snip of the result. 

The Grand Total for Facility Active Days should have been 150, but it is displaying 186.

Thanks,

[amitchandak · Accepted solution]

@SimonGoldPPG , Assuming your row label is region, Try like

SUMX(values('Facility Region'[Region]), calculate(COUNTROWS(DISTINCT('Global Daily Census'[CensusDate]))))

[SimonGoldPPG]

Adding Calculate worked. 

Thanks,

[Anonymous]

Hi @SimonGoldPPG ,

Please update the formula of measure [Facility Active Days] as below and check if you can get the correct grand total values:

Facility Active Days :=
SUMX (
    VALUES ( 'Facility Region'[Facilitiy Name] ),
    COUNTROWS ( DISTINCT ( 'Global Daily Census'[CensusDate] ) )
)

You can also refer the methods in the following links to resolve it.

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Obtaining accurate totals in DAX

Dax for Power BI: Fixing Incorrect Measure Totals

If you still can't get the correct result, please provide some sample data in your model(exclude sensitive data).

Best Regards

Rena