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
JorisDecoene
Regular Visitor

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 turnover and KPI.jpg

Dataset 1:

 

dataset waste mass.jpg

 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
Ashish_Mathur
Super User
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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:

WASTEYearQuarter
70,882020-1
50,342020-2
4,92020-3

 

DATASET 2:

Relative Turnover [PLN]YearQuarter
30000002019-1
30000002019-2
30000002019-3
30000002019-4
10000002020-1
10000002020-2
8000002020-3

 

 

MEASURE - KPI (export from line-graph showing KPI over YearQuarter from first dataset):

KPI WASTEYearQuarter
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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Great, that works !

Thanks a lot for your help and quick response, sir.

 

Kind regards,

Joris

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-stephen-msft
Community Support
Community Support

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]

4.png5.png

 

3.Create a relationship as follows.

6.png

 

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.

7.png

 

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

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.