Forum Discussion

vicks's avatar
vicks
Frequent Visitor
8 years ago
Solved

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;     i tried to find out how to solved...
Need Help
Show and Tell
Tips and Tricks
  • MFelix's avatar
    MFelix
    8 years ago

    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.

     

     

    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] )
)

     

    Regards,

    MFelix

MFelix's avatar
MFelix
Icon for Super User rankSuper User
8 years ago

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.

 

 

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] )
)

 

Regards,

MFelix

vicks's avatar
vicks
Frequent Visitor
8 years ago

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