Measure for Average displayed in Card Visual

[deboec]

Hello guys,

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

Business Unit	Cost
1	1,000
2	2,000
3	1,500
4	2,500
Total	7,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

[PaulOlding · Accepted solution]

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]

)

[Anonymous]

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:

Date	User	Application
12/14/2022	user1	Tool 1
12/14/2022	user2	Tool 2
12/14/2022	user1	Tool 1
12/19/2022	user2	Tool 3
12/27/2022	user3	Tool 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

[deboec]

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 · Accepted solution]

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]

)

[PaulOlding]

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]

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 · Accepted solution]

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]

)

[Anonymous]

 please Can i have Weekly Avg?
 thanks 

[deboec]

Thank you very much!
Exactly what I was looking for.
Do you mind briefly explaning what the "@" in the function does?

[PaulOlding]

The @ doesn't do anything.  It's just a naming convention used by these guys https://www.sqlbi.com/ .

They explain why in this video https://www.sqlbi.com/tv/naming-temporary-columns-in-dax/ 

TL;DR "a naming convention for temporary columns in DAX expressions to avoid ambiguity with the measure reference notation."

[deboec]

I do have one more question about your sintax:

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?