Average of sum

[Switto]

Hi All,

I am trying to find out the Average of Processes(category) with different tree sizes.

I have taken all my eligible time and category from different tables and put it as Measured columns.

I am able to take the average for the tree sizes but unable to get the sum of the category tree size as it is showing the average of the total.

For example:

	>500	0-50	51-500	Sum
Mandatory	12516	4018	5372	21906
Option		7749		7749

As you can see in the able Mandatory is showing 21906 however in powerbi it is showing 4290.

I have a excel file but don't know how to upload it here to give more clarity.

thanks

[Switto]

Hi All,

I have used the below post :

 https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

and created three measures

mAvgof>500 = CALCULATE(AVERAGE(RawFile[mEligible_Time]),
    FILTER(
        ALLEXCEPT(RawFile,RawFile[mCategory]),RawFile[mTree_size]=">500"))/3600

mAvgof51-500 = CALCULATE(AVERAGE(RawFile[mEligible_Time]),
    FILTER(
        ALLEXCEPT(RawFile,RawFile[mCategory]),RawFile[mTree_size]="51-500"))/3600

mAvgof0-50 = CALCULATE(AVERAGE(RawFile[mEligible_Time]),
    FILTER(
        ALLEXCEPT(RawFile,RawFile[mCategory]),RawFile[mTree_size]="0-50"))

and I have taken sum of all three and got the result by applying the slicer on mCategory.

As the data in seconds, I have divided the measures with 3600 so it will give in Hours but is it possible to get in HH:MM format?

[Switto]

Hi All,

I have achieved the numbers by using the three measures. However, while applying the Date filters, it doesn't move. It is always static. I don't understand the reason. 

[v-deddai1-msft]

Hi @Switto ,

Have you try the measure I provided?

Best Regards,

Dedmon Dai

[v-deddai1-msft · Accepted solution]

Hi @Switto ,

Just use the following measure in your matrix:

Measure =
IF (
    ISFILTERED ( Table1[ID] ),
    AVERAGE ( Table1[mEligible_Time] ),
    SUMX (
        SUMMARIZE (
            Table1,
            Table1[Process Name],
            Table1[ID],
            "ave", AVERAGE ( Table1[mEligible_Time] )
        ),
        [ave]
    )
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[harshnathani]

HI @Switto ,

To convert to HH:MM

https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486

https://community.powerbi.com/t5/Desktop/Convert-seconds-column-into-duration-column-hh-mm-ss-ss-nee...

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[jthomson]

Maybe you could try doing something like:

MeasureToFixSum = 
var a = calculate([existingthing], category = "0-50")

var b = calculate([existingthing], category = "51-500")

var c = calculate([existingthing], category = ">500")

return a+b+c

That should prevent the average spreading out over the data as a whole while still retaining the split you want - that's worded as a measure so I'm not sure how that'd work with you seemingly saying you've got calculated columns?

[Switto]

@jthomson Thanks for the reply.

I tried it, however, is not auto-populating the column I want(category which is measured column). 

Thanks for your time 🙂

[Ajinkya369]

Hi @Switto ,

upload your excel file on onedrive or sharepoint and you can paste the link here.

Thank you

[Switto]

@Ajinkya369 : Thanks for the quick replay. Please see the below link.

https://1drv.ms/x/s!ApxV4iLm5c5ea2yyL3C7-c57g5c?e=VqLlsy

Thanks