Calculate the difference between two percentage columns

[Power_BI_306]

Hi Everyone,

I’m new to Power BI and need some help trying to figure out how to calculate the difference between columns set as a percentage.

% of AMOUNT = is simply the sum of all cost by Model set to show as a percentage

% of LIC_PLATE = is a disticnt count of all licence plates by model set as a percentage

AMOUNT % vs LIC_PLATE  % DIFFERENCE = This is where I need help. I would like this column to show the difference between % of AMOUNT and % of LIC_PLATE. Essentially (LIC_PLATE  %  - AMOUNT %)

For example, BERLINGO would be -1.80, TRANSIT would be 1.03.

Any help would be greatly appreciated!

Thanks

[Greg_Deckler]

Are % of AMOUNT and % of LIC_PLATE calculated columns or measures?

[Power_BI_306]

No, '% Amount' is a column containing costs set to show as percentage and '% of LIC_PLATE' is a column showing a distinct count of licence plates set to show as a percentage.

[Greg_Deckler]

OK, it would help to have example source data, but I'll give it whirl:

Measure = 
// This is your first percentage
VAR __%amount = SUM([Amount]) / SUMX(ALL('Table'),[Amount]))
// This is your second percentage
VAR __%lic = DISTINCT('Table'[Lic_Plate]) / DISTINCT(SELECTCOLUMNS(ALL('Table'),"__licPlate",[Lic_Plate]))
RETURN
__%amount - __%lic

[Power_BI_306 · Accepted solution]

Thanks Greg, your solution gave me a good starting place. I eventually created three measures which worked perfectly.

Amount% = SUM(Query1[AMOUNT]) / SUMX(ALLSELECTED(Query1),(Query1[AMOUNT]))

Fleet% = VAR LP = SUMMARIZE(ALLSELECTED(Query1),Query1[LIC_PLATE],"count",DISTINCTCOUNT(Query1[LIC_PLATE]))
RETURN
DIVIDE(CALCULATE(DISTINCTCOUNT(Query1[LIC_PLATE]),ALLSELECTED(Query1[LIC_PLATE])),SUMX(LP,[count]))

Fleet vs Amount = [Fleet%] - [Amount%]