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 all,
I've been struggling finding the right solution for calculating a KPI based on 2 datasets.
I try to clarify the topic in short:
1) dataset 1: contains a column with a value for 'mass' (a number) as well as a column 'year' and a column 'quarter' (multiple rows in a certain quarter)
2) dataset 2: contains a column with a value for 'turnover' (a number), as well a column 'year' and a column 'quarter' (only one row in a certain quarter)
The KPI is as follows: mass / turnover (percentage)
However I need this per quarter; so the KPI is mass of a given year/quarter divided by turnover in that same quarter.
I tried to do this as follows:
Created a measure 'total mass' under dataset 1: total mass = sum(mass)
Created a measure 'KPI' under dataset 2: KPI = total mass / turnover
I expected Power BI to take the total mass automatically in that same quarter as the turnover, but that's not the case.
The value he takes is always the grand total of all mass (see screenshot below: column 'waste check'.)
How can I have the measure 'total mass' being taken from exact the same year and quarter as a given turnover value?
I hope this a bit clear.
Thanks a lot for any help !!
PS maybe these screenshots below will help to understand.
Kind regards
Joris
Dataset 2:
Dataset 1:
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
In each of the 2 datasets, we will need to create a Date column so that we can in turn build a Calendar table. A Date in each dataset cannot be built unless we have a month column as well. So if in each dataset, you can create a month column (could be the first month of the quarter), then i can help.
Also, please share both datasets in a format then can be pasted in MS Excel.
Hi Ashish,
Thanks for your reply. Please have a look at my answer on Stephen's reply - I made the relationship already between the 2 datasets (based on the column YearQuarter). Still a problem with the calculated turnover though.
Hereby the datasets:
DATASET 1:
WASTE | YearQuarter |
70,88 | 2020-1 |
50,34 | 2020-2 |
4,9 | 2020-3 |
DATASET 2:
Relative Turnover [PLN] | YearQuarter |
3000000 | 2019-1 |
3000000 | 2019-2 |
3000000 | 2019-3 |
3000000 | 2019-4 |
1000000 | 2020-1 |
1000000 | 2020-2 |
800000 | 2020-3 |
MEASURE - KPI (export from line-graph showing KPI over YearQuarter from first dataset):
KPI WASTE | YearQuarter |
0,000480% | 2020-1 |
0,000340% | 2020-2 |
0,000030% | 2020-3 |
so this KPI is not yet correct as it calculates the TOTAL turnover in a given quarter.
I hope everything is clear. Looking forward to your reply.
Kind regards
Joris
Hi,
You may download my PBI file from here.
Hope this helps.
Great, that works !
Thanks a lot for your help and quick response, sir.
Kind regards,
Joris
You are welcome.
Hi @JorisDecoene ,
1.Based on your picture, I created the following sample data myself.
Dataset 1:
mass | Quarter | Year |
0.7 | 1 | 2019 |
0.7 | 1 | 2020 |
0.7 | 1 | 2020 |
0.7 | 1 | 2020 |
0.7 | 1 | 2020 |
0.7 | 1 | 2020 |
0.7 | 1 | 2020 |
0.7 | 1 | 2020 |
Dataset 1:
turnover | Year | Quarter |
3000000 | 2019 | 1 |
3000000 | 2019 | 2 |
3000000 | 2019 | 3 |
3000000 | 2019 | 4 |
1000000 | 2020 | 1 |
1000000 | 2020 | 2 |
1000000 | 2020 | 3 |
1000000 | 2020 | 4 |
2.Create a column named YearQuarter in both tables.
YearQuarter = [Year]&"-"&[Quarter]
3.Create a relationship as follows.
4.Create a measure to calculate the percent.
Measure =
CALCULATE (
SUM ( 'Dataset 1'[mass] ),
ALLEXCEPT ( 'Dataset 1', 'Dataset 1'[YearQuarter] )
)
/ CALCULATE (
SUM ( 'Dataset 2'[turnover] ),
ALLEXCEPT ( 'Dataset 2', 'Dataset 2'[YearQuarter] )
)
5.The result is this. Click the percent sign, because the value is too small, you need to keep multiple decimal places.
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Stephen,
thanks a lot for your effort to resolve this issue. Very much appreciated!
I think I'm almost there, however I don't yet get the right values. When I open your file, I see the same problem:
For example, KPI in 2020-1:
Mass in 2020-1 : 7*0.7 = 4.9 ton
Turnover in 2020-1 : 1.000.000
KPI = 4.9 / 1.000.000
However the value in this table is calculated by dividing mass in quarter by TOTAL turnover:
= 4.9 / 16000000 = 0.0003063%
Kind regards and thanks once again,
Joris
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 |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |