Dynamic Difference of Percentage of Column Total

[jeffgarlisch]

Hello All Im working with a matrix that looks like so. 

I am calculating the % Diff column using these three DAX formulas

Calculates % of Column Total for all Categories

%OfAllTotal = Divide([Total Qual],CALCULATE([Total Qual],ALL(QUALNUMJULY[Qualification],QUALNUMJULY[CAI],QUALNUMJULY[DBAName],QUALNUMJULY[AccountNumber])))

Calculates total records

Total Qual = COUNT(QUALNUMJULY[Qualification])

Calculates the % Difference between Column Totals Based on available data sources

% Diff All = CALCULATE(QUALNUMJULY[%OfAllTotal],QUALNUMJULY[SourceIdentification] = "<A>") - CALCULATE(CALCULATE(QUALNUMJULY[%OfAllTotal],QUALNUMJULY[SourceIdentification] = "<Y>"))

The % Difference should always be calculated as DataSource A - DataSource Y.

At the moment there are only two data sources, in the future there will be many more, DataSource C, D, E....

Datasource Y will be a constant value, and Datasource A will change per Datasource.

My question is if there is a way to calculate the difference in % of column total dynamically, where I dont have to write a separate formula for each data source as they appear in the data? 

Thanks in Advance!

[v-piga-msft]

Hi @jeffgarlisch,

Is the table structure of your DataSource A, B, C the same?

In additon, I'm a little confused about that what you want to do.

Do you want to replace the Data source A with C,D,E? Or you will add more data source for this report and calculate the Data source A - DataSource Y, Data source C - DataSource Y  and Data source D - DataSource Y?

Best  Regards,

Cherry

[jeffgarlisch]

Hi @v-piga-msft thanks for your reply!

Its all one single dataset but with an ID column for the source of the data. Which at the moment only contains two distinct IDs, but will later expand. 

So like you said I will be adding more datasources for this report and looking to have columns displaying the distinct  % difference for each indiviudal data source arranged in a matrix visual style similar to the one below, but with added column headers for each new data source.. 

Heres a mockup example I made in Gimp. It should look like this. 

Thanks!