Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
hi all,
created 2 measures, first to have previous year population and second to calculate percentage of current (this) year and last year, match formula: ((this year pop - last year pop)/ this year pop)*100
previous year shows correctly, but percent change (which is just have an extended calculation in measure for previous year population) not showing data in 2 types of visuals (see screenshot) :
1) how it can appear - what i am doing wrong or missing here
2) how it can be used as Country wise Average column / Measure to create further visuals table/chart/graph.
pct chg =
var __currentcountry = MAX('Population'[Country Name])
var __thisyearpop = MAX('Population'[this year])
var __thisyear = MAX('Population'[year])
var __lastyear = MAXX(
FILTER(
ALL('Population'), 'Population'[Country Name] = __currentcountry && 'Population'[year] < __thisyear), 'Population'[year])
var __lastyearpop = CALCULATE( MAX('Population'[this year] ), 'Population'[Country Name]= __currentcountry, 'Population'[year]= __lastyear)
var __pctchg = IF (__lastyearpop > 0,
CALCULATE(((__thisyearpop-__lastyearpop)/__thisyearpop)*100,
'Population'[Country Name]= __currentcountry, 'Population'[Year]= __thisyear))
return __pctchg
regards
Solved! Go to Solution.
Hi, @Anonymous
Please change the formula of [New pct change] as below:
New pct change =
VAR tab =
ADDCOLUMNS (
SUMMARIZE ( Population, Population[Country Name], Population[Year] ),
"pct change", [pct change]
)
RETURN
AVERAGEX ( tab, [pct change] )
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi all,
Matrix visual showing wrong totals for Measures, how totals can be handled to have correct totals? please help to solve this problem. Last Year Pop and pct change both are Measures. and also please guide how I can hide ( if wants to ) total for a specific column, Not All columns totals.
regards
not worked for me, the blank value for New Pct Change:
here are all 3 measure used to achieve this :
================================
regards
Hi there.
Your methods look a bit unorthodox. Have a look at creating a separate calendar table first. Your Population table shouldn't have a year column, just your Calendar table.
Here's the basics, the next article in the series goes through one way of creating the calendar table.
https://www.sqlbi.com/articles/automatic-time-intelligence-in-power-bi/
Edit: oh yeah and no need to multiply with 100, changing the measure type to percents does the job for you.
thank you @Gabriel_Walkman , i have changed the formula to :
regards
Hi, @Anonymous
Sorry, calculating the subtotal value line by line, I did not find any errors. Can you explain in detail your expected results?
Best Regards,
Community Support Team _ Eason
please see the separate topic created yesterday: Topic Link
expected result for Pct Change column is country wise AVERAGE of Pct Change values, e.g. Afghanistan should have 2.88%
regards
I don't think your problem is not in the percentage (though I'd recommend to always use DIVIDE instead of the "/" symbol, no need for the >0 then.).
Your "this year pop" subtotals behave differently than your "Last Year Pop". As you can see, this year subtotal maybe sums the values, but Last Year Pop does not.
I recommend that you start from the beginning, and use a separate calendar table as I suggested.
I can't wrap my mind around your setup. Having both [Year] and [this year] makes no sense to me.
1) Year is a year column like: 2017, 2018 ...
2) There is no Date column in data i received, just year wise populaton in each country ... will this work when create a Caledar table and relate it with Population ?
3) do not have " this year " column / measure, but have " this year pop " and this has same value as my column " PreviousYr" and in my measure to calculate Pct Change it was not accepted so i created a measure
4) as we can see the first row of every country has Sub Totals where in this case Afghanistan showing 82.59% (instead of 2.88%) and Albania 83.39% here should be the AVERAGE of all Pct Change values by country.
also i have created a separate thread/topic for this purpose here: Topic Link
regards
Try this
click on pct change > column tools > Summarization > Average
Then on the matrix value list change pct change to average.
Maybe the same for [this year]? Though then this and last year work on different logic.
after click and right click on "pct change" i failed to see the options " column tools > Summarization > Average " because Column Tools is a contextual tab option, what i can see is Measure Tools tab instead.
below are 3 measures " this year pop " ... " last year pop " and " pct change " ( please guide if mistake is here due to my lack of understanding )
regards
Column tools can be found at the top, also visible in your screenshot. So no need for right clicking at all. But I hope the solution from v-easonf-msft works for you.
yeah, in first screenshot, and i have selected a measure " pct chg ", but now i can't see it when i have or not Selected a measure, yes when i am selecting a column it appears.
regards
Hi, @Anonymous
Please change the formula of [New pct change] as below:
New pct change =
VAR tab =
ADDCOLUMNS (
SUMMARIZE ( Population, Population[Country Name], Population[Year] ),
"pct change", [pct change]
)
RETURN
AVERAGEX ( tab, [pct change] )
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
thanks a bunch @v-easonf-msft , @Gabriel_Walkman for your passions to guide us, help us, you people here are much much helpful to new comers like me 😊 appreciate your struggle all the time to make us Learn actually.
@v-easonf-msft your last formula to achieve this works fine:
New pct change =
VAR tab =
ADDCOLUMNS (
SUMMARIZE ( Population, Population[Country Name], Population[Year] ),
"pct change", [pct change]
)
RETURN
AVERAGEX ( tab, [pct change] )
kind regards
any hope to get help?
both measures " last year pop " and above mentioned " pct chg " are approx same , just added last formula for " __pctchg " ... the " last year pop " is appearing in visual but " pct chg " not appeared but only its total appeared, what the thing i missed or doing wrong? please help.
regards
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |