Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Divide each row value with total row total

 

Hi,

 

I have a matrix with "fg year and month rows"  and "received date" in the column.

the matrix shows the count of fg in each received month. I want to add a % for each value using the fg total row (row level value divided by fg total value. 

 

Example data like below.

Oct 2021 rows and Oct 2021 column the % should be 100%(4622/fg total 6422)

Nov 2021 row and Oct 2021 column the % should be  59.04%(4652/fg total 7883), Nov 2021 column 40.96%(3229/7883)

and so on...  


Trying out this code and gives me wrong % value.

DIVIDE(SUM(table[FG count]),CALCULATE(SUM(table[FG Count]),ALLEXCEPT(table,table[DateFG])),0)

 

Appreciate any help.

 

Kindest regards

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous , already resolved the issue. Please see below the code. btw, thanks for the reply.

    % of total =

    VAR fg_volume = CALCULATE([fg count],ALL(table[Receipt Month]), ALL(table[FG date]))
    RETURN DIVIDE([fg], fg_volume)

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    What is the [DateFG] in the formula?

    As I can see from the screenshot, the row total should be:

    CALCULATE (
    SUM ( 'table'[FG Count] ),
    FILTER (
        ALL ( 'table' ),
        'table'[year] = SELECTEDVALUE ( 'table'[year] )
            && 'table'[month] = SELECTEDVALUE ( 'table'[month] )
    )
)

     

    Best Regards,

    Jay

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous , already resolved the issue. Please see below the code. btw, thanks for the reply.

      % of total =

      VAR fg_volume = CALCULATE([fg count],ALL(table[Receipt Month]), ALL(table[FG date]))
      RETURN DIVIDE([fg], fg_volume)