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