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,
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
Year | Month | Municipality | Key Figure | Aggregation Type (Numerator) | Aggregation Type (Denominator) | Numerator | Denominator | Result |
2017 | Januar | Aarhus | Forbrugspct, serviceudg ift korr. budget | LastNonEmpty | LastNonEmpty | 1,09E+09 | 1,29E+10 | 0,08 |
2017 | Januar | Aarhus | Pct. alle anbragte - plejefam. (alle) | AverageOfChildren | AverageOfChildren | 332,21 | 644,42 | 0,52 |
2017 | Januar | Aarhus | Pct. udg. samlet - forebyg. samlet | Sum | Sum | 16861026 | 52430748 | 0,32 |
2017 | Januar | Aarhus | Samlede udg. (service) pr 0-22-år | Sum | AverageOfChildren | 52430748 | 94702 | 553,64 |
2017 | Januar | Aarhus | Strukturel driftsbalance | Sum | FirstNonEmpty | 72096098 | 334441 | 215,57 |
2017 | Januar | Aarhus | Udvikling, antal 85+ årige (pct.) | LastNonEmpty | FirstNonEmpty | 5496 | 5496 | 1 |
2017 | Januar | København | Forbrugspct, serviceudg ift korr. budget | LastNonEmpty | LastNonEmpty | 2,03E+09 | 2,46E+10 | 0,08 |
2017 | Januar | København | Pct. alle anbragte - plejefam. (alle) | AverageOfChildren | AverageOfChildren | 34,93 | 68,85 | 0,51 |
2017 | Januar | København | Pct. udg. samlet - forebyg. samlet | Sum | Sum | 37818493 | 1,37E+08 | 0,28 |
2017 | Januar | København | Samlede udg. (service) pr 0-22-år | Sum | AverageOfChildren | 1,37E+08 | 148960 | 919,4 |
2017 | Januar | København | Strukturel driftsbalance | Sum | FirstNonEmpty | 3,21E+09 | 599226 | 5361,89 |
2017 | Januar | København | Udvikling, antal 85+ årige (pct.) | LastNonEmpty | FirstNonEmpty | 6872 | 6872 | 1 |
2017 | Februar | Aarhus | Forbrugspct, serviceudg ift korr. budget | LastNonEmpty | LastNonEmpty | 2,01E+09 | 1,29E+10 | 0,16 |
2017 | Februar | Aarhus | Pct. alle anbragte - plejefam. (alle) | AverageOfChildren | AverageOfChildren | 329,85 | 650,96 | 0,51 |
2017 | Februar | Aarhus | Pct. udg. samlet - forebyg. samlet | Sum | Sum | 20403847 | 57923628 | 0,35 |
2017 | Februar | Aarhus | Samlede udg. (service) pr 0-22-år | Sum | AverageOfChildren | 57923628 | 94615 | 612,2 |
2017 | Februar | Aarhus | Udvikling, antal 85+ årige (pct.) | LastNonEmpty | FirstNonEmpty | 5446 | 5496 | 0,99 |
2017 | Februar | København | Forbrugspct, serviceudg ift korr. budget | LastNonEmpty | LastNonEmpty | 3,87E+09 | 2,46E+10 | 0,16 |
2017 | Februar | København | Pct. alle anbragte - plejefam. (alle) | AverageOfChildren | AverageOfChildren | 35 | 68,1 | 0,51 |
2017 | Februar | København | Pct. udg. samlet - forebyg. samlet | Sum | Sum | 43048915 | 1,37E+08 | 0,31 |
2017 | Februar | København | Samlede udg. (service) pr 0-22-år | Sum | AverageOfChildren | 1,37E+08 | 149354 | 915,61 |
2017 | Februar | København | Udvikling, antal 85+ årige (pct.) | LastNonEmpty | FirstNonEmpty | 6798 | 6872 | 0,99 |
Any help is greatly appreciated!
Bump
Hi @Anonymous ,
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
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.
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 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |