Skip to main content
cancel
Showing results for 
Search instead 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

Reply
SimonGoldPPG
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.

 

SimonGoldPPG_0-1602194682448.png

 

Thanks,

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

 

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

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

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

 

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

Adding Calculate worked. 

 

Thanks,

 

v-yiruan-msft
Community Support
Community Support

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

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors