Forum Discussion
Calculate the difference between two percentage columns
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
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%]
4 Replies
- Greg_Deckler
Community Champion
Are % of AMOUNT and % of LIC_PLATE calculated columns or measures?
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
