cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vicks
Frequent Visitor

How to average value with group by and summarized

Hello;

 

as a relative newbie to power bi and DAX, i tried to create my own dataset to practice dax syntax, but then i am confused how to solved this;

 

dataset.jpg

 

i tried to find out how to solved this, but i can't find the related issue, would you please to show me the right dax syntax to solved this;

 

any guidance on achieving this will be much appreciated

 

warm regards;

vick

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @vicks,

 

The calculation of measures in dax is based on context so if you make any calculation based on the columns you select to give context to your information it will give you the result you need.

 

In this case and since you want the average you only need to create a simple measure of average:

 

Average qty = AVERAGE('Dataset'[Qty])

 

As you can see below the values gives for the average are as you need. However this result can also be achieve choosing the Average when adding the column Qty to your visuals without the need for any calculations.

 

If you want to have a more complex formula using the division you should use the following formula:

 

Avr Divide = DIVIDE(SUM('Dataset'[Qty]);COUNT('Dataset'[GroupProduct]) )

As you can see because the formulas are based on context it knows that you are grouping the information so no need to put it on your formula.

 

group.png

 

However as you can see the result based on context doesn't give you the correct amount on total line so you need to add additional context to your last row redo your measure to:

 

Average qty Total =
IF (
    HASONEFILTER ( 'Dataset'[GroupProduct] );
    [Average qty];
    SUMX ( ALL ( 'Dataset'[GroupProduct] ); [Average qty] )
)

total.png

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

5 REPLIES 5
MFelix
Super User
Super User

Hi @vicks,

 

The calculation of measures in dax is based on context so if you make any calculation based on the columns you select to give context to your information it will give you the result you need.

 

In this case and since you want the average you only need to create a simple measure of average:

 

Average qty = AVERAGE('Dataset'[Qty])

 

As you can see below the values gives for the average are as you need. However this result can also be achieve choosing the Average when adding the column Qty to your visuals without the need for any calculations.

 

If you want to have a more complex formula using the division you should use the following formula:

 

Avr Divide = DIVIDE(SUM('Dataset'[Qty]);COUNT('Dataset'[GroupProduct]) )

As you can see because the formulas are based on context it knows that you are grouping the information so no need to put it on your formula.

 

group.png

 

However as you can see the result based on context doesn't give you the correct amount on total line so you need to add additional context to your last row redo your measure to:

 

Average qty Total =
IF (
    HASONEFILTER ( 'Dataset'[GroupProduct] );
    [Average qty];
    SUMX ( ALL ( 'Dataset'[GroupProduct] ); [Average qty] )
)

total.png

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



vicks
Frequent Visitor

Dear Felix;

 

After redo  step by step i found the "litlle" missing thing, i just typo the dax syntax;

that should be 

AvqQtyTotal = IF(HASONEFILTER('MyDataset'[GroupProduct]),[AvqQty],SUMX(ALL('MyDataset'[GroupProduct]),[AvqQty]))

what i wrote 

AvqQtyTotal = IF(HASONEFILTER('MyDataset'[GroupProduct]),[AvqQty],SUMX(ALL(MyDataset'[GroupProduct]),[AvqQty]))

 

now the result as i expected ;

 

Thank you very much for your help, i can closed this issue, and that would be belong to your suggestion;

 

warm regards;

 

vick

vicks
Frequent Visitor

Dear Felix;

 

thank you very much for your response, i tried to follow step by step the guidance you gave to me, but i still found mistaken value when create measure for TotalAverageQty; did i write wrong syntax, or i missed some thing here;

 

here is my resultTotalAverageQty still not correct valueTotalAverageQty still not correct value

Anonymous
Not applicable

Do you want to get the following table regarding the product average?

1522328209439.jpgIf this is the case, you can use the following DAX get this it

 

AverageProduct = GROUPBY('Dateset_Table','Dateset_Table'[GroupProduct],"AverageQty",AVERAGEX(CURRENTGROUP(),'Dateset_Table'[Qty]))

When creating the vis_table in Power BI, remeber to change the value AverageQty to Average.

WX20180329-205741@2x.png

 

 

 

 

 

 

vicks
Frequent Visitor

Hi Jessica;

 

thank you very much for your reply, i tried to follow your guidance but different result i got, did i missed some thing here?

 

jessica-PBI.png

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!