I need a DAX measure to calculate an average of scores by selected agents over selected months
I have been trying to use the following measure to create a column in my Visual Table to give me the average of each agent for a group of selected months. If I change the slicer for different months it gives me the average for each month instead of averaging them together. Can you help?: Average Agent Score for Months = CALCULATE( average(TestScores[Score]) ,FILTER(ALL(TestScores), TestScores[Agent] IN VALUES(TestScores[Agent])) ,FILTER(ALL(TestScores), TestScores[Month] IN VALUES(TestScores[Month])) ) Here is what I get: Example: For Barney, I expect to see the average of 39 ((39+48+34)/3) in all three places. and if I change the months, I expect to see the average recalculated for the new group of months. I am using a test table of data: Month Agent Score Team JAN Bill 32 Barney FEB Bill 32 Barney MAR Bill 37 Barney APR Bill 33 Barney MAY Bill 48 Barney JUN Bill 40 Barney JUL Bill 37 Barney AUG Bill 45 Barney JAN Mary 43 Barney FEB Mary 32 Barney MAR Mary 50 Barney APR Mary 43 Barney MAY Mary 46 Barney JUN Mary 50 Barney JUL Mary 46 Barney AUG Mary 50 Barney JAN Raval 46 Mavis FEB Raval 41 Mavis MAR Raval 43 Mavis APR Raval 43 Mavis MAY Raval 37 Mavis JUN Raval 39 Mavis JUL Raval 35 Mavis AUG Raval 30 Mavis JAN Luego 30 Mavis FEB Luego 33 Mavis MAR Luego 33 Mavis APR Luego 50 Mavis MAY Luego 41 Mavis JUN Luego 34 Mavis JUL Luego 36 Mavis AUG Luego 38 Mavis
How can I find out why standard deviation measure doesn't work?
Hello Everyone, I have 3 tables and appended on the top of each other. Each table I can identify with table_type column, so that I can use necessary aggregations by filtering them. They have attribute and value column so I can fetch the data in dax (please see below) VAR _table = ADDCOLUMNS( 'Appended Table', "IsNumeric", IF( ISERROR( VALUE('Appended Table'[Value]) ), 0, VALUE('Appended Table'[Value]) ), "Table Type", 'Appended Table'[Table_Type], "UID", 'Appended Table'[Attribute] ) VAR _measure = STDEVX.P( FILTER(_table, [Table Type]= "My Table"), [IsNumeric] ) RETURN _measure okay I realize it works when I filter one category which values are 0.170289855 and 0.239130435 and their standard deviation is 3.44% and when I filter another category which values are 0 0.006122449 0.020408163 -0.037037037 -0.048247999 -0.092592593 -0.100040016 -0.123193614 -0.145365551 0.183673469 -0.195918367 0.285714286 0.287504476 -0.306122449 -0.322942857 0.36122449 -0.413793103 -0.624752187 and measure returns = 6.05% however when I look at excel and calculated std.p = 24.68% I need to figure it out somehow... Since I'm sure I'm working on the right selected (filtered data) and not able to dissect the formula and try to replicate step by step... I need your help= any ideas, directions will be much appreciated. thank you !Issue with IFERROR Not Catching Errors Row by Row When Using YIELD in Power BI
Hello, I'm experiencing an issue with the YIELD function in Power BI. I'm trying to calculate the yield, but it seems that IFERROR is not catching errors on a row-by-row basis and assigning values when an error is detected. Here are snapshots of my Excel and Power BI setup: Excel Snapshot: Power BI Snapshot: The formula I'm using in Power BI is: YIELD = YIELD(test[SETTLEMENT_DATE], test[MATURITY], test[COUPON], test[LAST_PRICE], test[PAR], test[Freq]) All columns seem to have the correct data types, but I still encounter #ERROR values. In Excel, I get #NUM! and some valid yield values as expected. However, in Power BI, I was expecting it to show results row-by-row and leave blank if there's an error, instead of showing an error for the whole column. I tried wrapping the YIELD function with IFERROR, but it doesn't seem to handle the errors as expected. Is there any way to make Power BI show the result row-by-row and leave the cell blank if it encounters an error? Attached the data here for reference SETTLEMENT_DATE MATURITY COUPON LAST_PRICE PAR Freq 07/30/2024 07/31/2024 11.00% 9 100 4 07/30/2024 08/31/2024 9.40% 94 100 4 07/30/2024 08/31/2024 9.40% 95 100 4 Thank you!
Dynamic Dax Measure
Hi guys, I am working on a dynamic dax measure which I like to use for one visual. Basically I want to have one visual that shows the costs. Based on the dynamic filter which I put into the x-axis field, the user is able to filter by various categories which come from various data sources. All are string values e.g by category, by city etc. I tried so many things but the Code just doesnt work. I dont want to create a extra table and merge all these categories inside, I just want it to work how it is. Thank you in advance! DynamicMeasureNEW = VAR SelectedColumn = SELECTEDVALUE(DynamicTable[Column1]) RETURN SWITCH ( SelectedColumn, "Delay Reason", VALUES(MedicallyFitForDischarge[FIT_REASON]), "Care Group", VALUES(Look_New_Trust_Hierarchy[CARE_GROUP]), "Specialty", VALUES(Look_Specialty[Specialty Description]), "Ward", VALUES(MedicallyFitForDischarge[LOCATION]), BLANK() // Return BLANK() for unmatched cases )
