Forum Discussion

cschuchert's avatar
cschuchert
Frequent Visitor
7 years ago
Solved

Summin a calculated weighted average in a Matrix Column subtotal

I have a simple calculated weighted average formua which works exactly as expected: Totals = SUMX('Table', Table[Forecast_Value]*Table[DaysInMth])/SUMX('Table', Table[DaysInMth])   The Matrix disp...
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi cschuchert

     

    You may refer to below measure.

    Totals =
SUMX ( 'TableA', TableA[Forecast_Value] * TableA[DaysInMth] )
    / SUMX ( 'TableA', TableA[DaysInMth] )
    Subtotal =
VAR a =
    SUMMARIZE ( TableA, TableA[Division], "b", [Totals] )
RETURN
    SUMX ( a, [b] )

     

    Regards,

    Cherie

  • cschuchert's avatar
    cschuchert
    7 years ago

    You are a genius!! I saw your post where it looked like I hadn't sent the data-I'm not sure what happened  to my post with the excel data but it's there now.

    I cannot thank you enough!! I'll have to research SUMMARIZE so I understand how to apply it to other visualizations.

     

     

    Regards,

    Carolyn

Anonymous's avatar
Anonymous
Not applicable
7 years ago

cschuchert,

 

Can you send the result Matrix before and after the adition of Subtotals,

That way I can Help you.

 

 

  • cschuchert's avatar
    cschuchert
    Frequent Visitor
    7 years ago

    See screenshots. Let me know if you only wanted the data.

    • cschuchert's avatar
      cschuchert
      Frequent Visitor
      7 years ago

      AfterBeforeExpected Outcome

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hello cschuchert,

         

        Now I understand what You need.

        The aswer is pretty simple after you discover, and you will be making the same fix a lot of times =).

         

        The DAX is doing the same measure you create for the totals, It consider the total line as another row in the table, that way the results in error for you that want the totals of lines.

        For this treatment you will need to tell DAX that if you filter the column "Product_Type" you want the sum of row.

         

        You will use IF plus HASONEFILTER or ISFILTERED, you chose one or another.

         

        Like:

         

        Measure = IF ( ISFILTERED ( ColumnName[Product_Type] ) ; Sum(TableName[Category] ; SUMX ( 'Table', Table[Forecast_Value] * Table[DaysInMth]) / SUMX ( 'Table', Table[DaysInMth] ) )

         

        Cheers,