Percent of Total issues...

Question

Post reply

Percent of Total issues...

pietlinden

SSC Guru

Points: 63545
More actions
August 21, 2016 at 11:36 pm

#312640

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63545 More actions · Answer 1

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 Points: 1004543 More actions · Answer 2

I guess my question would be, why are you using PowerPivot for this calculation to begin with? Why not "just" use T-SQL?

--Jeff Moden

RBAR 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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63545 More actions · Answer 3

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 Points: 1004543 More actions · Answer 4

Ah... got it. Thanks for the time for the response.

--Jeff Moden

RBAR 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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Percent of Total issues...

Cookies on SQLServerCentral