Measure (which is in %) totalling up above 100%.. instead i want 'Average' of this column

[navedkhan]

Hi All - need help on column total exceeding 100% of my measure.

Names	Productivity %
Meena	83.8%
Ramesh	96.8%
Suresh	84.4%
Total	265.0%

This should ideally show 'Avg. of the column';

Names	Productivity %
Meena	83.8%
Ramesh	96.8%
Suresh	84.4%
Total	88.3%

any help please would be much appreciated...

@Zubair_Muhammad 

[MFelix]

Hi @navedkhan ,

Is the Productivity % a column  on your dataset?

If yes then on your table visual select the productivity % and average as summarize.

As a best practice all percentages should be calculated in PBI as a measure and not as a calculated column.

Can you share some information regarding the dataset and the calculation of the productivity %?

Regards,

MFelix

[navedkhan]

Hi @MFelix 

No its a measure (productivity) which is calculated basis couple of filters in the table. so it's not a column. So i need to just do an 'Avg' of the totals instead of 'Sum' for this measure.

Hope this helps...?

[Zubair_Muhammad]

@navedkhan 

Whats your formule for the MEASURE?

[navedkhan]

@Zubair_Muhammad 

this MEASURE is made up of various filters/flags in the table. Actually it measures time spent by each agent on the floor and then divide by 480 hrs to calculate per day productivity.

So when am arranging this in table, line items is showing fine but when you look at Totals it is summing up the entire values rather i would like to display it as Average.

[MFelix]

Hi @navedkhan ,

If you measure is the sum of time spent divided by 480 hours, that is the calculation you are getting on the overall total so is the sum of all the agents divided by 480.

You should use a AVERAGEX to calculate your total result something like:

Average =
IF (
    HASONEFILTER ( Table[Name] );
    [Measure];
    AVERAGEX ( ALLSELECTED ( Table[Name] ); [Measure] )
)

You must replace the values by the column names of your model and the Measure by the calculation you make.

Regards,

MFelix

[navedkhan]

Hi @MFelix 

@Zubair_Muhammad 

this is not working exactly the way i wanted it to. as per you logic, when am using this instead of my earlier MEASURE, its impacting my row level line items, instead i want only 'TOTALS' to reflect as 'Average' of this MEASURE which is used in table (and appearing as Column)

See below this is what i would have done in Excel. Same i wanted in Powerbi through DAX. (my rows remain relevant as % while Totals only to reflect one AVG. number)

[TomMartens]

Hey,

maybe this is what you are looking for:

Measure = 
AVERAGEX(
    'Table1'
    ,[Productivity %]
)

At least, based on your sample data from your initial post, the result for the total looks like your expected result:

Regards,

Tom

[navedkhan]

@TomMartens 

nope it doesn't resolves my problem.

Row level Measure to show as in the pic shows, however only TOTALS need to show 'Average' and not 'Sum', bcoz 265% is incorrect as Sum of %.

Am not sure how do i re-surrect my data points here but ask is very simple.

@Zubair_Muhammad  ??

[TomMartens]

Hey @navedkhan 

please provide a pbix with sample data that reflects your data model especially a measure that "simulates" the measure [Productivity %].

Regards,

Tom