Percent of Total issues...

• pietlinden

SSC Guru

Points: 62772

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

Points: 62772

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: 996463

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

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Forum FAQ

• pietlinden

SSC Guru

Points: 62772

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: 996463

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