cancel
Showing results for
Did you mean:
Frequent Visitor

## 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 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

2 ACCEPTED SOLUTIONS
Resolver III

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]
)
Resolver III

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]
)
8 REPLIES 8
Frequent Visitor

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?

Resolver III

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]
)
Resolver III

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(
"@Cost", [Cost]
),
[@Cost]
)
Frequent Visitor

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

Resolver III

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]
)
Frequent Visitor

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

Resolver III

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

Frequent Visitor

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?

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

#### Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors