Forum Discussion

deboec's avatar
deboec
Icon for Helper I rankHelper I
5 years ago
Solved

Measure for Average displayed in Card Visual

Hello guys,

 

I am displaying a Matrix visual in Power BI that shows me a specific value for business units.

 

Business UnitCost
11,000
22,000
31,500
42,500
Total7,000

 

The Cost Value in the visual contains a measure which looks something like this:

Cost =
CALCULATE(
          SUM(table1[values]),
          FILTER(table2, table2[header] = "costs"),
          FILTER(table3, NOT(table3[business_units] = "2222")

)

 

I want to add a slicer visual to be able to select a specific date so the matrix visual shows the values for the date i select in the slicer.

 

Now I also want to add a Card visual that shows me the Average of the [Cost] measure values depending on the dates I selected in the slicer.

 

For example: If I select Jan 2020, Feb 2020 and March 2020 the card visual shows the Average [Cost] for the selected months.

If I select Jan - Dec 2020 the card visual shows the Average of the whole 12 months in the year.

 

How do I write a measure to give me such an interactive Average?

 

Thank you

average
DAX
  • PaulOlding's avatar
    PaulOlding
    5 years ago

    OK, you'll need to replace Table[Business Unit] with year and month columns from your date table.

     

    Avg per Month =
    AVERAGEX(
    SUMMARIZECOLUMNS(Date[Year], Date[Month],
    "@Cost", [Cost]
    ),
    [@Cost]
    )
  • PaulOlding's avatar
    PaulOlding
    5 years ago

    Ok, the SUMMARIZECOLUMNS could be a problem in that case.

     

    We can change that to a SUMMARIZE.

     

    Avg per Month =
    AVERAGEX(
    SUMMARIZE(DIM_DATE, DIM_DATE[Year], DIM_DATE[Month]),
    [Cost Measure]
    )

10 Replies

  • PaulOlding's avatar
    PaulOlding
    Icon for Solution Sage rankSolution Sage
    5 years ago

    Do you mean the average per business unit?

    eg.  for your table above the card would display 1750..

     

    A measure to do that would look like

    Avg per Business Unit =
    AVERAGEX(
    SUMMARIZECOLUMNS(Table[Business Unit],
    "@Cost", [Cost]
    ),
    [@Cost]
    )
    • deboec's avatar
      deboec
      Icon for Helper I rankHelper I
      5 years ago

      I am looking for the average of the totals.

       

      Let's say the table above displays January 2020. If I select only January 2020 in the slicer I want the output "7,000".

      Let's say the total of [Cost] across all business units in February 2020 is 5,000. If I select January 2020 and February 2020 in the slicer the output should be "6,000".

      • PaulOlding's avatar
        PaulOlding
        Icon for Solution Sage rankSolution Sage
        5 years ago

        OK, you'll need to replace Table[Business Unit] with year and month columns from your date table.

         

        Avg per Month =
        AVERAGEX(
        SUMMARIZECOLUMNS(Date[Year], Date[Month],
        "@Cost", [Cost]
        ),
        [@Cost]
        )
  • deboec's avatar
    deboec
    Icon for Helper I rankHelper I
    5 years ago

    If I use this part of your sintax:
    SUMMARIZECOLUMNS(Date[Year], Date[Month],

     

    and I have 1 slicer visual in my canvas for Date[Year] and 1 slicer visual for Date[Month] to choose different years the measure doesn't work.
    "Can't display the visual".
    I can select 1 or more Months (e.g. Jan, Feb & Mar) but I cannot select a year.

    Any ideas?

    • PaulOlding's avatar
      PaulOlding
      Icon for Solution Sage rankSolution Sage
      5 years ago

      Ok, the SUMMARIZECOLUMNS could be a problem in that case.

       

      We can change that to a SUMMARIZE.

       

      Avg per Month =
      AVERAGEX(
      SUMMARIZE(DIM_DATE, DIM_DATE[Year], DIM_DATE[Month]),
      [Cost Measure]
      )
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, I'm having the same issue for creating a filterable average in a card visualization.

    I would like a card to display an average tool usage based upon a tool and the amount selected on a slicer.

    I am very new to DAX formulas so any help would be great.  Here is a sample of the data:

    DateUserApplication
    12/14/2022user1Tool 1
    12/14/2022user2Tool 2
    12/14/2022user1Tool 1
    12/19/2022user2Tool 3 
    12/27/2022user3Tool 2

     

    Imagine 80K rows of this data highlighting tool and user usage per day (each login is its own row).  I need a formula that will average that information based on upon the timeframe selected on the slicer.

    Thanks for your help.

    Tom