Forum Discussion
Solved
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 Mo...
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%]
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
Community Champion
Community ChampionOK, 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
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%]