Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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%]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
78 | |
75 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |