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.
I have a measure that does calculations on rows in some columns and then returns values in a new column.
I now need to take the average of that measure and display it in a second new column. I treid the following but it didn't work.
What are the columns in your visual? Typically, this is done with an expression like this (replace with your table and column name, or add multiple columns to the ALLSELECTED.
Avg Task Score = AVERAGEX(ALLSELECTED(Table[Column]), [Task Score])
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
That worked to get the AVG Task Score to calculate. I now have a column with tha same value in every row. The value is equal to the average of Task Score.
Do you have any thoughts on how to group the results so that I get a differnet value for each group? In other words, I have a table with the following. The Task Score is a measure.
A-----B-----C-----Group-----Task Score
1-----1-----1-----A-----------3
2-----1-----2-----A-----------5
1-----3-----0-----B-----------4
2-----2-----2-----B-----------6
I need the AVG Task Score to look like this:
A-----B-----C-----Group-----Task Score-----AVG Task Score
1-----1-----1-----A-----------3---------------4
2-----1-----2-----A-----------5---------------4
1-----3-----0-----B-----------4---------------5
2-----2-----2-----B-----------6---------------5
Ultimately, I have groups and each group gets a score on certian tasks. I need to show the average score per group.
Please try this expression
Group Avg =
CALCULATE (
SUMX (
SUMMARIZE (
Table,
Table[A],
Table[B],
Table[C]
),
[Task Score]
),
ALL (
Table[A],
Table[B],
Table[C]
)
)
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I am not following. I am not sure what to use for Table[A], Table[B], Table[C]. My task score measure has conditional statments in it. I also don't understand how the column Group is taken into account.
Does groupby work? I tried the following but get an error.
I thought A B and C were columns in your visual. If you do have columns other than the Group column, you can use the same pattern. Summarize over the non-group columns, and remove the filters from the non-group columns with ALL().
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
I think I might have confused things when I tried to simplify things in the example of my table. This is closer to what I am really looking at.
Attribute 1-----Attribute 2-----Attriubute 3-----Group-----Task Score-----AVG Task Score
Buy Book------Amanda--------1/1/2021--------A-----------3---------------4.5
Sell Book------John------------2/1/2021--------A-----------5---------------4.5
Fly Home-----Tom-------------11/14/2020------B-----------4---------------4.5
Win Game----Sam-------------10/12/2020------B-----------6---------------4.5
Task Score and AVG Task Score are Meaures.
I need a third measure that caclulates AVG Task Score per Group. For example:
Attribute 1-----Attribute 2-----Attriubute 3-----Group-----Task Score-----AVG Task Score---Group Score
Buy Book------Amanda--------1/1/2021--------A-----------3---------------4.5-----------------4
Sell Book------John------------2/1/2021--------A-----------5---------------4.5-----------------4
Fly Home-----Tom-------------11/14/2020------B-----------4---------------4.5-----------------5
Win Game----Sam-------------10/12/2020------B-----------6---------------4.5-----------------5
NOTE: There are over 10 attribute columns.
Hi @Anonymous,
In fact, power bi not suitable to write DAX formula to value across multiple fields dynamically, you need to hard code in your expressions.
In my opinion, I'd like to suggest you consider using the 'unpivot columns' feature to convert your value fields to 'attribute' and 'value'.
Unpivot columns (Power Query)
Then you can simply write formulas to calculate across raw table fields and create matrix visual to display them.
BTW, if you want to do aggregate with measure formulas, you can use the summarize function to create a template table with the current category and measure formal, then you can use the iterator function to apply second aggregate functions.
Measure Totals, The Final Word
Regards,
Xiaoxin Sheng
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |