Divide Dax Formula by category

[Anonymous]

Hi everyone,

I wish to have a visual card of the information which has been circled in red at the end of the image.

I am trying to obtain the "total Project Average" which can be seen in the bottom of the first image to display this info on a card visual.

The column "Actual Weighted Completion" is calculated by multiplying the columns of "Completion" and "Weighted Task" together.

This total project average is calculated by summing the "Actual Weighted Completion" according to the respective project (e.g. Assignment 1). So the summation of the "Actual Weighted Completion" for assignment 1 is 56%, for assignment 2 is 0% and for assignment 3 is 100%.

Hence getting the average of these 3, you get the value of the "Total Project Average" of 52%. 

If you divide (to get the average) only the "Actual Weighted Completion" column you get the result of 10%, which is not the result I wish to obtain.

Was wondering if I could get some assistance on this and help me out.

Thank you in advance. 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try below steps:

1. below is my test table

Table:

2. please try to create a new measure with below dax formula

Measure =
VAR tmp =
    SUMMARIZE (
        'Table',
        [Project],
        "Sum_CPL", SUM ( 'Table'[Actual Weighted Completion] )
    )
VAR _val =
    AVERAGEX ( tmp, [Sum_CPL] )
RETURN
    _val

3. add a card visual with measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

hi @Anonymous thank you so much for taking the time to help me out. Will give it a try, thanks again!