Forum Discussion
KPI calculated per quarter per year (2 different datasets)
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:
7 Replies
Hi Anonymous ,
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
- Ashish_Mathur
Super User
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- Ashish_Mathur