Forum Discussion

ooctaviano's avatar
ooctaviano
Frequent Visitor
5 years ago
Solved

Change Subtotals

I have the folling Matrix

 

The Goal is calculated in a measure that summarizes the goal for the day

The Average Result is calculated in a measured that is an average of the results per day

I need the subtotal for goal stay as a sum, but the total for average result to be a sum for all the days instead of the general average. 
How can I configure this.

Please advise

Omar Octaviano

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    You could make use of the function HASONEVALUE(). It checks if the field has only one value or not.

     

    For example,

    Average Measure = 
IF(
    HASONEVALUE(SampleData[WeekDay]),
    "Calculate Average",
    "Calculate Sum Of Average"
)

     

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  ooctaviano  ,

    According to your description, I create this data:

    Here are the steps you can follow：

    1. Create measure.

    Goal = SUM('Table'[amount])
    Average =
AVERAGE('Table'[amount])

    Put into the matrix as:

    Average_Result =
VAR _NEW = SUMMARIZE('Table','Table'[Weekday],"_AVG",[Average])
return
IF(HASONEVALUE( 'Table'[Weekday]),[Average],SUMX(_NEW,[_AVG])
)

    2. Result:

    You can downloaded PBIX file from here.

     

    Best Regards,

    Liu Yang

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    You could make use of the function HASONEVALUE(). It checks if the field has only one value or not.

     

    For example,

    Average Measure = 
IF(
    HASONEVALUE(SampleData[WeekDay]),
    "Calculate Average",
    "Calculate Sum Of Average"
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  ooctaviano  ,

    According to your description, I create this data:

    Here are the steps you can follow：

    1. Create measure.

    Goal = SUM('Table'[amount])
    Average =
AVERAGE('Table'[amount])

    Put into the matrix as:

    Average_Result =
VAR _NEW = SUMMARIZE('Table','Table'[Weekday],"_AVG",[Average])
return
IF(HASONEVALUE( 'Table'[Weekday]),[Average],SUMX(_NEW,[_AVG])
)

    2. Result:

    You can downloaded PBIX file from here.

     

    Best Regards,

    Liu Yang

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