Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
OK, you'll need to replace Table[Business Unit] with year and month columns from your date table.
Ok, the SUMMARIZECOLUMNS could be a problem in that case.
We can change that to a SUMMARIZE.
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
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.
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
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.
please Can i have Weekly Avg?
thanks
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
53 | |
46 | |
16 | |
12 |