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

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,

    • cschuchert's avatar
      cschuchert
      Frequent Visitor
      7 years ago

      The Matrix in my screenshots is only filtered by the Quarter. I also have multiple slicers on the page for the Forecast week, Division, ProductType and Quarter used in another Matrix on the same page. The Matrix using the meaure is to get a CWA for each Quarter; all filters work on both Matrixes.

      If I filter this to one Division the Total is correct but if all Divisions are selected the Total is the Average.

       

      What exactly is  Sum(TableName[Category] doing in the measure example? I understand an IF statement needs a True/False but all I really want is for this measure to Sum the values used in my original measure.