Forum Discussion
Helper IDAX Measure does not give average in total column
Hello, sshanssun ,
the easiest solution would be to utilize a virtual table to first summarize it and then do average over it.
Two new measures like this:EfficencyFixed = var _virtualTable = SUMMARIZE(Data, Data[Emp Name], Data[Date].[Day], "value", [Efficiency]) var _average = AVERAGEX( _virtualTable, [value]) return _averageProdPerHourFixed = var _virtualTable = SUMMARIZE(Data, Data[Emp Name], Data[Date].[Day], "value", [Prod per hour]) var _average = AVERAGEX( _virtualTable, [value]) return _average
Please note, this will only work with Emp Name and Day of Date, as this is kinda hardcoded sumarization. It would be also better to have Calendar, so you don't have use [Date].[Day]
Super UserHello, sshanssun ,
I have troubles opening your file, it feels like it's damaged, can you try to share it again?
Generally, Totals in PBI doesn't show SUM as in Total in Excel for example. Total generally shows the same measure just without the filter context of the columns or rows. If you want Average, you gotta use average function or if you wanna sum but in Total you want average, you need to play with functions like "HASONEVALUE" or "HASONEFILTER" and then specify what should happen when it means the condition.
Helper IHi, Thank you for the response, please see if the below link is working to access pbix file,
https://drive.google.com/file/d/10Ln1kpnttDV-uHxzaUg25ZjEjKqlHPpU/view?usp=drivesdk
- vojtechsima
Hello, sshanssun ,
the easiest solution would be to utilize a virtual table to first summarize it and then do average over it.
Two new measures like this:EfficencyFixed = var _virtualTable = SUMMARIZE(Data, Data[Emp Name], Data[Date].[Day], "value", [Efficiency]) var _average = AVERAGEX( _virtualTable, [value]) return _averageProdPerHourFixed = var _virtualTable = SUMMARIZE(Data, Data[Emp Name], Data[Date].[Day], "value", [Prod per hour]) var _average = AVERAGEX( _virtualTable, [value]) return _average
Please note, this will only work with Emp Name and Day of Date, as this is kinda hardcoded sumarization. It would be also better to have Calendar, so you don't have use [Date].[Day]- sshanssun
Hi vojtechsima - It worked like a charm. Thank you so much!
- vojtechsima
sshanssun happy to help. kudos appretiated.