Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
57 | |
22 | |
18 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |