Work around for matrix summing instead of averaging column values

[Anonymous]

Hi,

I have a matrix (attached). My data atrribute is "production plant" and then a percentage by month by plant. As you can see by the image, the total for the row is an average of the values (by plant). When I total the column however, it takes the SUM of the numerator regardless of plant divided by sum of the denominator regardless of plant. 

In other words, if

Plant 1 - denominator is 10, numerator is 20

Plant 2 - denominator is 20, numerator is 100

It's taking 30/130 instead of the average of both equations (10 divided by 20, 20 divided by 100). 

Is there a way I can create a work around for this? 

My numerator is a SUM

My denominator is comprised of 3 calcs x * y * z

                  x is a min calc

                  y is a sumx

                  z is a mix of a bunch of different types of calcs

Thanks in advance

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can take a look at the following blog to know how to use 'if statement' with filters to check and find out 'total' level, then you can write a formula to manually summarize records and calculation to replace original result.

Clever Hierarchy Handling in DAX 

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[amitchandak]

ideally when you do product the it should sum(A*B*C)

Not the workaround is choose a level and use summarize

sumx(summarize(table,table[level],"_A",sum(A),"_B",sum(B),"_C",sum(C)),[_A]*[_B]*[C])

A, B, C can be from different tables join to one dim.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin