cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
KL_Anders Frequent Visitor
Frequent Visitor

Conditional aggregation for years on key figures

Hi,

 

I have a big dataset for monthly key figures for the 98 Danish municipalities.

 

When I want to see the 3 measures - Numerator, Denominator and Result(Numerator/Denominator) - across YEARS, the aggregation in Power BI is to sum

 

 

YearMonthMunicipalityKey FigureAggregation Type (Numerator)Aggregation Type (Denominator)NumeratorDenominatorResult
2017JanuarAarhusForbrugspct, serviceudg ift korr. budgetLastNonEmptyLastNonEmpty1,09E+091,29E+100,08
2017JanuarAarhusPct. alle anbragte - plejefam. (alle)AverageOfChildrenAverageOfChildren332,21644,420,52
2017JanuarAarhusPct. udg. samlet - forebyg. samletSumSum16861026524307480,32
2017JanuarAarhusSamlede udg. (service) pr 0-22-årSumAverageOfChildren5243074894702553,64
2017JanuarAarhusStrukturel driftsbalanceSumFirstNonEmpty72096098334441215,57
2017JanuarAarhusUdvikling, antal 85+ årige (pct.)LastNonEmptyFirstNonEmpty549654961
2017JanuarKøbenhavnForbrugspct, serviceudg ift korr. budgetLastNonEmptyLastNonEmpty2,03E+092,46E+100,08
2017JanuarKøbenhavnPct. alle anbragte - plejefam. (alle)AverageOfChildrenAverageOfChildren34,9368,850,51
2017JanuarKøbenhavnPct. udg. samlet - forebyg. samletSumSum378184931,37E+080,28
2017JanuarKøbenhavnSamlede udg. (service) pr 0-22-årSumAverageOfChildren1,37E+08148960919,4
2017JanuarKøbenhavnStrukturel driftsbalanceSumFirstNonEmpty3,21E+095992265361,89
2017JanuarKøbenhavnUdvikling, antal 85+ årige (pct.)LastNonEmptyFirstNonEmpty687268721
2017FebruarAarhusForbrugspct, serviceudg ift korr. budgetLastNonEmptyLastNonEmpty2,01E+091,29E+100,16
2017FebruarAarhusPct. alle anbragte - plejefam. (alle)AverageOfChildrenAverageOfChildren329,85650,960,51
2017FebruarAarhusPct. udg. samlet - forebyg. samletSumSum20403847579236280,35
2017FebruarAarhusSamlede udg. (service) pr 0-22-årSumAverageOfChildren5792362894615612,2
2017FebruarAarhusUdvikling, antal 85+ årige (pct.)LastNonEmptyFirstNonEmpty544654960,99
2017FebruarKøbenhavnForbrugspct, serviceudg ift korr. budgetLastNonEmptyLastNonEmpty3,87E+092,46E+100,16
2017FebruarKøbenhavnPct. alle anbragte - plejefam. (alle)AverageOfChildrenAverageOfChildren3568,10,51
2017FebruarKøbenhavnPct. udg. samlet - forebyg. samletSumSum430489151,37E+080,31
2017FebruarKøbenhavnSamlede udg. (service) pr 0-22-årSumAverageOfChildren1,37E+08149354915,61
2017FebruarKøbenhavnUdvikling, antal 85+ årige (pct.)LastNonEmptyFirstNonEmpty679868720,99

 

Any help is greatly appreciated!

3 REPLIES 3
Community Support Team
Community Support Team

Re: Conditional aggregation for years on key figures

Hi @KL_Anders ,

Could you please share the expected result to us? Do you want to sum the three measure of every year?

If it is, we can use the calculate(sum(Numerator),allexcept(Year)).

Best Regards,

Teige

KL_Anders Frequent Visitor
Frequent Visitor

Re: Conditional aggregation for years on key figures

Hi @TeigeGao 

 

Eg. for the Key Figure "Samlede udg. (service) pr 0-22-år" I want an aggregation for a year such that the result equals

 

Sum(Numerator)/Average(Denominator)

 

In another case, I want the aggregation for the Key Figure "Pct. alle anbragte - plejefam. (alle)" to give the average of the numerator and the average of the denominator (here, the result will be the same if it's the sum of numerator divided by the sum denominator).

 

The aggregation has to depend on the "Aggregation Type" of the numerator and denominator.

KL_Anders Frequent Visitor
Frequent Visitor

Re: Conditional aggregation for years on key figures

Bump

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 42 members 952 guests
Please welcome our newest community members: