Column and Grand Total Help

[Anonymous]

Hi - I am new to Power BI and have been trying to figure this out for awhile now. Ive tried several things I read online and have had no luck. The first pic below is an example of what my data table looks like, the second I did in excel, it is the desired outcome in powerbi. The values for each row are a count of ordernbr showing as % of row total, however for the grand total row the ask is for an avg of those percentages. Also for the column total the ask is to show the total count of ordernbr. Is this possible without creating another table just to show the avg total? Thank you in advance.

[MohammadLoran25]

Hi @Anonymous ,

It is a good question.

Assume your table name is 'ProductNbrTable'

Follow steps below:

1-

OrderNbrCount =
COUNTROWS ( VALUES ( ProductNbrTable[OrderNbr] ) )

2-

OrderNbrCountRowGrandTotal =
CALCULATE ( [OrderNbrCount], ALLSELECTED ( ProductNbrTable[GroupTAT] ) )

3-

Ratio =
DIVIDE ( [OrderNbrCount], [OrderNbrCountRowGrandTotal] )

4-

AvgOfRowPercent =
DIVIDE (
    IF (
        HASONEVALUE ( ProductNbrTable[GroupTAT] ),
        CALCULATE ( SUMX ( VALUES ( ProductNbrTable[Product_Priority] ), [Ratio] ) )
    ),
    CALCULATE (
        COUNTROWS ( VALUES ( ProductNbrTable[Product_Priority] ) ),
        ALLSELECTED ( ProductNbrTable[GroupTAT] )
    )
)

Then by putting measures of step 2 & 4, you get exactly the result you need.

If this answer solves your problem, give it a thumbs up and accept it as a solution so the others would find what they need easier.

Regards,

Loran

[Anonymous]

Thank you for taking time to take a look at this. Ive done steps 1-4 as you stated but Im not sure how to get the outcome outlined in the original post based off this (mainly the total row and columns)? Thank you in advance for any additional detail you can provide.

[MohammadLoran25]

@Anonymous 

You did sth wrong I think because your result is different from what I got in my sample data based on your data. Make sure not to put the formula of a measure directly in another measure. Instead reference it. Like what I did in my 4 steps.

Then you just need to put the measures of step 2 & 4 in the matrix.

If you only want the Measure step 2 to be shown in the total (right section of matrix), you can omit the total of your first matrix (which in the last picture is 100%) and put the measure step 2 in a separate matrix and then put the second matrix exactly next to your first matrix To seems as a column of your first matrix.