Average between the total of two columns

[gwoodley]

Hi, I would like to calculate the average bewteen the total of two columns, currently I see the average of the average for each row.

To hopefully explain this better;

I have three rows showing planned vs completed and I want to calculate the percentage bewteen the total of each column.

Planned Total = 4

Completed Total = 5

Overall percentage in therefore 125% but I see 100%

I'm currently seeing the average of each average from each row

P02 Planned 1 - Completed 1 = 100%

P03 Planned 2 - Completed 4 = 200%

P04 Planned 1 - Completed 0 = 0%

Average from these three averages is 100% (300/3)

Is there a way I can caluclate the percentage from the column totals, rather than averaging the row totals?

Thanks

[Anonymous · Accepted solution]

Hi @gwoodley ,

I create a table as you mentioned.

Then I use matrix visual.

Next I create two measures and here are the DAX codes.

MEASURE =
DIVIDE ( SUM ( 'Table'[Completed] ), SUM ( 'Table'[Planned] ), BLANK () )

Measure 2 = 
VAR _vtable =
    SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[P], "A", 'Table'[Measure] )
RETURN
    IF ( 'Table'[Measure] = 1.25, 'Table'[Measure], AVERAGEX ( _vtable, [A] ) )

Finally you will get what you want.

Best Regards

Yilong Zhou

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

[gwoodley]

Thankyou, just what I wanted.

[Wilson_]

Hi gwoodley,

Hard to write out the measure without knowing anything about the structure of your data (or how you're calculating Completed or Planned), but the problem is you're dividing then aggregating, when you want to be aggregating then dividing.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.