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 ,

 

What is your measure TransactionsAgg[GLTotalAmount_Rev]? What does your data look like? Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

 

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

 

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

    I apologize that I was not clear.  That "TransactionsAgg[GLTotalAmount_Rev]" is the "Value" column you see in the matrix.  I have modified the data and the report so I could share pbix file and the matrix in question.  Please see the link below:

     

    https://1drv.ms/u/s!ArrJvtdfd3Ak6im_zT5KDplM4E-C?e=7DNBYu

     

    The problem I am having is that while the measure you provided works at the top level, it does not work for the levels beneath (highlighted in screen shot below):

     

    I hope this makes things more clear.  Thank you!