I have the following data: Production: - July 11,980 tonnes - August 10,789 tonnes - September 11,674 tonnes Energy Indices July 10,214 mega joules - August 10,378 mega joules - September 11,450 mega joules How can I calculate quarterly weighted average of energy in mega joules?
Explained in very detail. I'm finding it difficult to create virtual table as the calendar table is in an inactive relationship. Cumulative total is also not working here. Wt Actual R1 = VAR MaxDate=MAX(DimDate[Date]) VAR SumofWeightage = CALCULATE ( [Weightage Total], // Total for the period as denominator to calculate average weightage ALLSELECTED ( DimDate ), USERELATIONSHIP(DimDate[Date],'AMI TC Data'[Inspection Date]) ) RETURN VAR MonthlyTable = ADDCOLUMNS( SUMMARIZE( 'AMI TC Data', DimDate[Year], DimDate[MonthShort]), "MonthlyWt", [Weightage Total], "Wt", [Weightage Total]/SumofWeightage ) Return CALCULATE( SUMX( MonthlyTable, [MonthlyWt]*[Wt] ), USERELATIONSHIP('AMI TC Data'[Inspection Date], 'DimDate'[Date]) )
I don't understand the benefit of adding a weighted value when you can see the weight based on the Total Sales number. I still have to look for the highest number to see which one holds the most weight. I could do that just by looking at or sorting the Total Sales. Can someone give me a real-world example of how this is useful to you?
Thanks Chandeep. Quick question please. As you have already created 2x measures, any reason why you need to create a virtual table with 2x variables to get the weighted average rather than multiplying the 2x measures please? Thanks
Hi, its a ilustrative your class, i have a problem with the total of weigh ponderate, not is the sum, its the formula apply over total, can You help me to fix
Hello man, maybe, you can help I have a dataset - granularity is transaction by venture internal orders. Each venture has different set of shareholders. The required dashboard needs to show a chart showing each shareholder share $ in each of the projects /ventures. Still figuring out the DAX to use. Many thanks.
Hi - Thank you for sharing the video. It's very helpful. I have a similar situation. I have a table with data by Brand (total 3 Brand), Country, Product, Date (each day of the year) & working days , holiday and weekend flag against each day. I want to calculate a week ending 7th Jan or Week ending 14th Jan average transaction on a weighted working day (some country may have holiday). Since the data is at day level, I have duplicate dates, country & product etc. Is that possible, please?
Great video and very easy to follow !! How do I amend the DAX formula if the value for TOTAL changes with the filters? So if I wanted the weighted average only for quarter 1?
Sir, please guide if we have dates in column and row as string like sales, raw material, profit like, how to make measure so that if i filter sales and graph shows year by year sales of different companies, thanks in advance
these two examples and their weighted average does not look right. First, the weighted average needs two arrays. Here the second array is derived from the firstr array i.e. sales. The row level sale is divided by the grand total. Second thing, the values what we see from the acutals [total sales] and the [Weighted Average] are very far from each other. This does not seem right. Can someone throw some light here?
I'm new to dax Pls help with error debug. I'm trying to calculate sum of sales increase 19 vs 20 by Product and customer. Positive diff = Var a = distinct (sales[product]) Var b = district ( sales [customer]) Var c = a&b Return Sumx ( c, max(0,[val diff 19vs20])) Thank you.
Thanks dear. I am a little confused to get weighted average of my data. I have monthly production in tonnes and monthly energy in mega joules. I have to calculate weighted average of quarterly, half-yearly and annual energy indices in mega joules. Could you please guide me. Please share your WhatsApp no. So that I may show you my data and find the solution with your help.
It was a joke : ) It means that I thought I could do Power BI, but after this I have to tell my boss she should hire an expert on our BI reports instead of me, still being stuch with Sum and a few Calculate formulas : ) I took many online classes, but I guess that is just my level.
Love the honesty in your description dude.
This was GREAT! Exactly what I was looking for and explained so well!
Thanks! I needed to understand this quickly :)
Beautiful explaination!
Thanks, that summarize table helped me out!
Glad it was helpful 💚
Thank you my man, works with all kind of other columns! Good stuff :)
I have the following data:
Production:
- July 11,980 tonnes
- August 10,789 tonnes
- September 11,674 tonnes
Energy Indices
July 10,214 mega joules
- August 10,378 mega joules
- September 11,450 mega joules
How can I calculate quarterly weighted average of energy in mega joules?
Expert! Great pace too.
Explained in very detail. I'm finding it difficult to create virtual table as the calendar table is in an inactive relationship. Cumulative total is also not working here.
Wt Actual R1 =
VAR MaxDate=MAX(DimDate[Date])
VAR SumofWeightage =
CALCULATE (
[Weightage Total],
// Total for the period as denominator to calculate average weightage
ALLSELECTED ( DimDate ),
USERELATIONSHIP(DimDate[Date],'AMI TC Data'[Inspection Date])
)
RETURN
VAR MonthlyTable =
ADDCOLUMNS(
SUMMARIZE(
'AMI TC Data',
DimDate[Year], DimDate[MonthShort]),
"MonthlyWt", [Weightage Total],
"Wt", [Weightage Total]/SumofWeightage
)
Return
CALCULATE(
SUMX(
MonthlyTable,
[MonthlyWt]*[Wt]
),
USERELATIONSHIP('AMI TC Data'[Inspection Date], 'DimDate'[Date])
)
Wt Actual R1 =
VAR MaxDate = MAX(DimDate[Date])
RETURN
CALCULATE(
SUMX(
'AMI TC Data',
'AMI TC Data'[Weightage Total] * 'AMI TC Data'[Weightage Total] / CALCULATE(
SUM('AMI TC Data'[Weightage Total]),
ALL('AMI TC Data'),
DimDate[Date] IN DATESBETWEEN(
DimDate[Date],
BLANK(),
MaxDate
)
)
),
USERELATIONSHIP('AMI TC Data'[Inspection Date], DimDate[Date])
)
Thank you Paaji , This Video really saved my day today !
Happy to help!
Can you show this in power query?
I don't understand the benefit of adding a weighted value when you can see the weight based on the Total Sales number. I still have to look for the highest number to see which one holds the most weight. I could do that just by looking at or sorting the Total Sales. Can someone give me a real-world example of how this is useful to you?
Thanks Chandeep. Quick question please. As you have already created 2x measures, any reason why you need to create a virtual table with 2x variables to get the weighted average rather than multiplying the 2x measures please?
Thanks
Great video and explanation, thanks
Glad you enjoyed it!
Why would you weigh monthly sales by monthly sales.. is there any practical use for this?
Hi Chandeep. Thanks for the video. Good tips and techniques there. Will come in handy in my work. Thanks for sharing. Thumbs up!!
Thanks Wayne, Glad I could help
Cheers
Hey Chand thumbs up
Why can’t we have the new columns added within summarize . Than using add columns top of summarize. Please explain
Excellent!
Hi, its a ilustrative your class, i have a problem with the total of weigh ponderate, not is the sum, its the formula apply over total, can You help me to fix
Hello man,
maybe, you can help
I have a dataset - granularity is transaction by venture internal orders. Each venture has different set of shareholders.
The required dashboard needs to show a chart showing each shareholder share $ in each of the projects /ventures.
Still figuring out the DAX to use. Many thanks.
Fantastic! 👍
Do I need to use filter function for weighted average calculation?
I have a question of the same nature though requires some more expertise, how can I reach you ..
hi Sir,
How i can download the sample pbix file to practice ?
Hi - Thank you for sharing the video. It's very helpful. I have a similar situation. I have a table with data by Brand (total 3 Brand), Country, Product, Date (each day of the year) & working days , holiday and weekend flag against each day. I want to calculate a week ending 7th Jan or Week ending 14th Jan average transaction on a weighted working day (some country may have holiday). Since the data is at day level, I have duplicate dates, country & product etc. Is that possible, please?
Great Video
Great video and very easy to follow !! How do I amend the DAX formula if the value for TOTAL changes with the filters? So if I wanted the weighted average only for quarter 1?
Try using allselected() dax instead of All() for denominator calculation. Hope this helps
Sir, please guide if we have dates in column and row as string like sales, raw material, profit like, how to make measure so that if i filter sales and graph shows year by year sales of different companies, thanks in advance
Thanks!
Yoo.. thanks so much!!
these two examples and their weighted average does not look right. First, the weighted average needs two arrays. Here the second array is derived from the firstr array i.e. sales. The row level sale is divided by the grand total.
Second thing, the values what we see from the acutals [total sales] and the [Weighted Average] are very far from each other. This does not seem right. Can someone throw some light here?
How can I bueno a course of power bi?
I'm new to dax
Pls help with error debug.
I'm trying to calculate sum of sales increase 19 vs 20 by Product and customer.
Positive diff =
Var a = distinct (sales[product])
Var b = district ( sales [customer])
Var c = a&b
Return
Sumx ( c, max(0,[val diff 19vs20]))
Thank you.
In ytd wt avg total not sum
Thanks dear. I am a little confused to get weighted average of my data.
I have monthly production in tonnes and monthly energy in mega joules. I have to calculate weighted average of quarterly, half-yearly and annual energy indices in mega joules. Could you please guide me. Please share your WhatsApp no. So that I may show you my data and find the solution with your help.
Awesome.. could please share the source code (PBIX)?
goodly.co.in/weighted-average-in-power-bi/
Whoa. Whoosh.
After watching this video, I went to my boss and quit my job : )
Why..? I didn't promote any million dollar idea in the video.🤔
It was a joke : ) It means that I thought I could do Power BI, but after this I have to tell my boss she should hire an expert on our BI reports instead of me, still being stuch with Sum and a few Calculate formulas : ) I took many online classes, but I guess that is just my level.
great video, but content a bit too complicated for me ;p
No worries.. keep working you'll get there! :)