Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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%]
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.
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
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%]
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |