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
asap
Frequent Visitor

How to calculate average and add as a row?

I have 3 different tables that are connected by date: Budget, Plan and Actual. They all have same inidicators: Number of chickens and total weight.

 

Currently, I have such table:

Name Budgetplanactual
number of chicken, unit234
number of chicken, kg123
    

and I want to calculate average weight and visualise it with other two indicators. the formula is average weight = number of chicken, kg / number of chicken, unit.

I want such table:

Name Budgetplanactual
number of chicken, unit234
number of chicken, kg123
average weight chicken0,5000,6670,750

 

How can I accomplish that? I am limited with tables inside power bi

1 ACCEPTED SOLUTION

@asap 

pls see the attachment below.

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

Assuming number of chicken, unit and number of chicken, kg are 2 measures that you have already written, write this additional measure

[average weight chicken]=divide([number of chicken, kg],[number of chicken, unit])

Hope this helps.


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

@Ashish_Mathur unfortunately they are not measures. they are are in columns values and indicators. Furthermore, budget, actual and plan are separate tables

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/
ryan_mayu
Super User
Super User

@asap 

you can try with ISFILTER

budget2 = if(ISFILTERED('Table'[Name ]), sum('Table'[Budget]),CALCULATE(sum('Table'[Budget]),FILTER('Table','Table'[Name ]="number of chicken, kg"))/CALCULATE(sum('Table'[Budget]),FILTER('Table','Table'[Name ]="number of chicken, unit")))

1.PNG

ISFILTERED function (DAX) - DAX | Microsoft Docs





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




SHould i have to create 3 different measures for each table? If I create 3 different measure, how do i visualize them in one table?

could you pls provide the sample data?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I have calculate measure for each one and have following table

asap_0-1657250439107.png

So I want to lets say transpose the table which should look like:

Name Budgetplanactual
number of chicken, unit234
number of chicken, kg123
average weight chicken0,5000,6670,750

 

 

 

Actual

DateValueIndicator
29.12.202141 493chicken, unit
29.12.202140 343chicken, unit
29.12.202140 044chicken, unit
03.01.202236 744chicken, unit
03.01.202235 912chicken, unit
29.12.202193 079chicken, kg
29.12.202185 267chicken, kg
29.12.202183 878chicken, kg
03.01.202290 838chicken, kg
03.01.202285 395chicken, kg

 

Budget

DateValueIndicator
29.12.202150 000chicken, unit
29.12.202160 000chicken, unit
29.12.202150 000chicken, unit
03.01.202240 000chicken, unit
03.01.202230 000chicken, unit
29.12.202193 079chicken, kg
29.12.202185 267chicken, kg
29.12.202183 878chicken, kg
03.01.202290 838chicken, kg
03.01.202285 395chicken, kg

 

Plan

DateValueIndicator
29.12.202150 000chicken, unit
29.12.202160 000chicken, unit
29.12.202150 000chicken, unit
03.01.202240 000chicken, unit
03.01.202230 000chicken, unit
29.12.202193 079chicken, kg
29.12.202185 267chicken, kg
29.12.202183 878chicken, kg
03.01.202290 838chicken, kg
03.01.202285 395chicken, kg

@asap 

pls see the attachment below.

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.