Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Column and Grand Total Help

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 ...
General Comment
Show and Tell
Tips and Tricks
MohammadLoran25's avatar
MohammadLoran25
Solution Sage
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    MohammadLoran25
    Solution Sage
    3 years ago

    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.