## Percent of Total issues...

 Author Message pietlinden SSC Guru Group: General Forum Members Points: 56023 Visits: 17607 I have a Fact table that looks like this (it's a really small table... I know the design is less than ideal!!!):CREATE TABLE DiagnosisFact( PatientID INT, SymptomName VARCHAR(20), Severity TINYINT, ConsentDate DATE);I have a Symptom table...CREATE TABLE DimSymptom ( SymptomName VARCHAR(20) PRIMARY KEY);I can create the DiagnosisCount ... COUNTROWS('DiagnosisFact').The dumb thing I can't get to work is the Percentage of Total stuff. I must be missing something, but I can't override the SymptomName on the column or row...I thought it should be:Pct of Total:=DIVIDE( [Diagnosis Count], CALCULATE ( COUNTROWS('Diagnosis'),ALL('Diagnosis'[SymptomName]) ) )but no joy... what am I doing wrong?For grins, I did this in Excel 2016, and it worked... =CALCULATE ( [Total Sales], ALL( 'Calendar'[EnglishMonthName] ) )In terms of this dataset, it would be = DIVIDE ( [Diagnosis Count], CALCULATE ( [Diagnosis Count], ALL( 'Diagnosis'[SymptomName] ) ) )thanks,Pieter pietlinden SSC Guru Group: General Forum Members Points: 56023 Visits: 17607 I figured it out...`DIVIDE( [Symptom Count], CALCULATE(COUNTROWS('Diagnoses'),ALL('Diagnoses')) , BLANK())`One day I might maybe understand DAX, but that's not going to be anytime soon. Jeff Moden SSC Guru Group: General Forum Members Points: 913034 Visits: 48755 I guess my question would be, why are you using PowerPivot for this calculation to begin with? Why not "just" use T-SQL? --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Helpful Links:How to post code problemsHow to post performance problemsForum FAQs pietlinden SSC Guru Group: General Forum Members Points: 56023 Visits: 17607 Jeff,I would but then I would lose the ability to drill into it. It's the smallest data warehouse structure ever! 3 dimension tables and a fact table. Not terribly exciting, but I'm learning DAX, so I'm starting small.Besides, if I did it all in SQL Server, I'd never learn DAX at all...Pieter Jeff Moden SSC Guru Group: General Forum Members Points: 913034 Visits: 48755 Ah... got it. Thanks for the time for the response. --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair When you put the right degree of spin on it, the number 318 is also a glyph that describes the nature of a DBAs job. Helpful Links:How to post code problemsHow to post performance problemsForum FAQs