Forum Discussion

SMYAguirre's avatar
SMYAguirre
Frequent Visitor
3 years ago
Solved

Divide every row in a Matrix by a static calculated row

I am not entirely sure how to ask this question, but I will give it my best shot.  I am fairly new to Power BI, but worked in other data modeling/reporting systems.    We have found that income sta...
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi SMYAguirre ,

     

    Please try:

    Measure =
VAR _a =
    SUM ( TransactionsAgg[GLTotalAmount_Rev] )
VAR _b =
    CALCULATE (
        SUM ( TransactionsAgg[GLTotalAmount_Rev] ),
        FILTER ( ALL ( '..Report Structure' ), [GL Category] = "Revenue" ),
        REMOVEFILTERS (
            TransactionsAgg[ChartOfAccounts.GL Sub Category],
            TransactionsAgg[ChartOfAccounts.GL_Code_Desc]
        )
    )
RETURN
    DIVIDE ( _a, _b )

    Final output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

SMYAguirre's avatar
SMYAguirre
Frequent Visitor
3 years ago

Thank you. This partially worked, but not completely.

I used: 

Measure = DIVIDE(TransactionsAgg[GLTotalAmount_Rev]),CALCULATE(SUM(TransactionsAgg[GLTotalAmount_Rev]), FILTER(ALL(TransactionsAgg),[GL Category]="Revenue")))


So this is dividing the sum of GLTotalAmount by the sum of GLTotalAmount where the GL Category = Revenue, correct? That makes sense, but this is the result.

 

 

The levels below were not populated.  Is there a way to include the lowest level in that measure? 

Thank you again!!