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.
Hi,
I am using a "Line and clustered column chart" and would like to add an additional line to dsiplay the average value of one of the columns. I have written a DAX measure that pefectly calculates me the value that I want to see and I can use it on cards etc. However as soon as I put the measure on the chart, it is being calculated for each category on the x-axis again. Here is the structure of the measure that I use - it sums up values in a table (Table1) based on a filter in another table (Table2) and then divides by the number of selected entries in Table2.:
avg_activity =
VAR sumofactivity =
CALCULATE(
SUM('Table1'[Activity]);
'Table2'[Type] IN { "mytype" }
)
RETURN
VAR countofactivity =
CALCULATE(COUNTROWS(Table2);'Table2'[Type] IN { "mytype" })
RETURN
sumofactivity/countofactivity
The month year hierachy on the x-axis is also from Table2, the values on the chart are from Table1. Now what I would like to get when putting this measure on a the chart wozuld be something like this:
but what I get looks like this:
I've also tried using caluclated columns but with no success. I know that the problem is me using the Calculate-Function wrong but would be very thankful for any hint on how to get the desired behaviour!
Perhaps:
avg_activity =
VAR sumofactivity =
CALCULATE(
SUMX(ALL('Table1'),[Activity]);
'Table2'[Type] IN { "mytype" }
)
RETURN
VAR countofactivity =
CALCULATE(COUNTROWS(ALL(Table2));'Table2'[Type] IN { "mytype" })
RETURN
sumofactivity/countofactivity
Hi Greg, thanks for your quick response! Unfortunately that creates a value which is way too high, however at least it displays now a constant line, so I think you showed me the right track.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |