Forum Discussion

Power_BI_306's avatar
Power_BI_306
New Member
7 years ago
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...
Need Help
  • Power_BI_306's avatar
    Power_BI_306
    7 years ago

    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%]
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
7 years ago

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's avatar
Power_BI_306
New Member
7 years ago

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%]