topic Re: Measure for Average displayed in Card Visual in DAX Commands and Tips

Measure for Average displayed in Card Visual

deboec — Tue, 11 May 2021 15:00:51 GMT

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

Re: Measure for Average displayed in Card Visual

PaulOlding — Tue, 11 May 2021 16:43:57 GMT

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]

)

Re: Measure for Average displayed in Card Visual

deboec — Wed, 12 May 2021 06:30:01 GMT

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".

Re: Measure for Average displayed in Card Visual

PaulOlding — Wed, 12 May 2021 07:05:34 GMT

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]

)

Re: Measure for Average displayed in Card Visual

deboec — Wed, 12 May 2021 07:33:13 GMT

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

Re: Measure for Average displayed in Card Visual

PaulOlding — Wed, 12 May 2021 08:40:31 GMT

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."

Re: Measure for Average displayed in Card Visual

deboec — Fri, 14 May 2021 12:32:20 GMT

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?

Re: Measure for Average displayed in Card Visual

deboec — Mon, 17 May 2021 16:26:39 GMT

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

Any ideas?

Re: Measure for Average displayed in Card Visual

PaulOlding — Mon, 17 May 2021 17:12:08 GMT

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]

)

Re: Measure for Average displayed in Card Visual

Anonymous — Thu, 08 Dec 2022 14:44:26 GMT

please Can i have Weekly Avg?
thanks

Re: Measure for Average displayed in Card Visual

Anonymous — Wed, 14 Feb 2024 19:05:30 GMT

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