cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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,

1 ACCEPTED SOLUTION
Super User

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

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

3 REPLIES 3
Super User

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

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

Frequent Visitor

Thanks,

Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.