Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

measure value not appear in visual but its total appear??

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

 

 

 

Core2Plus_0-1623398176528.png

 

regards

 

1 ACCEPTED 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] )

 

 

 

17.png

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.

View solution in original post

17 REPLIES 17
Anonymous
Not applicable

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.

 

Core2Plus_0-1623718408240.png

regards

v-easonf-msft
Community Support
Community Support

Hi, @Anonymous 

Try to add a new measure as below to replace the field [pct change]

 

New pct change = AVERAGEX(Popolation,[pct change])

 

13.png

Best Regards,
Community Support Team _ Eason

 

Anonymous
Not applicable

not worked for me, the blank value for New Pct Change:

Core2Plus_0-1623811832169.png

here are all 3 measure used to achieve this :

this year pop = sum(Population[this year])
================================
Last Year Pop =
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)
return __lastyearpop
================================
pct change = IF (Population[Last Year Pop] > 0, CALCULATE(([this year pop]-[Last Year Pop])/[this year pop]))
================================
New pct change = AVERAGEX(Population,[pct change])    //this advised by @v-easonf-msft 

================================

regards

Gabriel_Walkman
Continued Contributor
Continued Contributor

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.

Anonymous
Not applicable

thank you @Gabriel_Walkman , i have changed the formula to :

pct change = IF (Population[Last Year Pop] > 0, CALCULATE(([This Year Pop]-[Last Year Pop])/[This Year Pop]))
here Last Year Pop and this year pop are both measures and working fine (screenshot below) after remove *100 and measure type to % ... but its not showing SubTotal as it should be, what is the problem with it, please advise.
Core2Plus_0-1623584242734.png

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

Anonymous
Not applicable

@v-easonf-msft 

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.

Anonymous
Not applicable

@Gabriel_Walkman 

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.

Anonymous
Not applicable

@Gabriel_Walkman 

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 )

 
this year pop = sum(Population[this year])  /* "this year" is column in Table which used in measure "this year pop", created this measure because it was not acceptable by measure "Pct Change" menitoned below */
================================
Last Year Pop =
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)
return __lastyearpop
================================
pct change = IF (Population[Last Year Pop] > 0, CALCULATE(([this year pop]-[Last Year Pop])/[this year pop]))
================================

 

regards

m3tr01d
Continued Contributor
Continued Contributor

@v-easonf-msft already gave you a working solution, Did you look at his Pbix?

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.

Anonymous
Not applicable

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

 

 

 

17.png

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.

Anonymous
Not applicable

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

 

Core2Plus_1-1623849019786.png

 

kind regards

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.