Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Sum of average columns

Hi, 

 

I am new to Power BI DAX function.

I have a fact table which is already calculate with sum of the count for each month(COUNT_UNIQUE_MTH), for each week (COUNT_UNIQUE_MTW), for each month to date (COUNT_UNIQUE_MTD), for each month (COUNT_UNIQUE_MTH). All of the sum is group by PRODUCT_CAT. At the same, the fact able store the date range.

I am able to show the column values for COUNT_UNIQUE_MTH by PRODUCT_CAT (38205 for PRODUCT_CAT = AA, 518592 for PRODUCT_CAT=BB) from the Lines and Stacked column chart by using the AVERAGE of COUNT_UNIQUE_MTH. However, I am now have an issue to show the total of both PRODUCT_CAT (556797 = 38205 + 518592) in the Lines Values of the chart. 

Appreciate your input on how to construct the formula?

Thanks.

Need Help

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Anonymous ,

     

    I am able to show the column values for COUNT_UNIQUE_MTH by PRODUCT_CAT (38205 for PRODUCT_CAT = AA, 518592 for PRODUCT_CAT=BB) from the Lines and Stacked column chart by using the AVERAGE of COUNT_UNIQUE_MTH. However, I am now have an issue to show the total of both PRODUCT_CAT (556797 = 38205 + 518592) in the Lines Values of the chart. 

    Generally, you could convert the measure "COUNT_UNIQUE_MTH" and "PRODUCT_CAT" to calculate column, and then create a measure by simply plus them. If you have other issues, please share some sample data, dax code and expected result.

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      It was not working.

      I tried to create the measure

      MTH_Average = CALCULATE(AVERAGE(UNIQUE_COUNT[COUNT_UNIQUE_MTH])|ALLEXCEPT(UNIQUE_COUNT|UNIQUE_COUNT[MONTH_FIRST_DAY]|UNIQUE_COUNT[PACKAGE_CAT]))
       
      it return me the correct average,
       
      I create another measure
      MTH_Total = calculate(SUMx(UNIQUE_COUNT|[MTH_Average]))
       
      it give me the wrong total of the average result.