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.

v-jianboli-msft's avatar
v-jianboli-msft
Icon for Community Support rankCommunity Support
3 years ago

Hi SMYAguirre ,

 

Based on your description, I have created a simple sample:

Please try:

 

Measure = DIVIDE(SUM('Table'[Value]),CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[GL Category]="Revenue")))

 

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.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

this worked for me! but when I apply a filter that changes the total amount in revenue it doesn't consider this change in revenue. For example, if revenue was 200 and then I apply a slicer and it becomes 100 it will still use the 200 revenue as the denominator.