cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
deboec
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 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

2 ACCEPTED SOLUTIONS

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

View solution in original post

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

View solution in original post

8 REPLIES 8
deboec
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?

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

View solution in original post

PaulOlding
Resolver III
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(
SUMMARIZECOLUMNS(Table[Business Unit],
"@Cost", [Cost]
),
[@Cost]
)

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

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

View solution in original post

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

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

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?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS on Demand

Microsoft Business Applications Summit sessions

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

Get Ready for Power BI Dev Camp

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