Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How to convert Power BI DAX to Oracle Analytics using calculation?

Received Response
194
Views
3
Comments
User_X8DWN
User_X8DWN Rank 1 - Community Starter
edited Apr 11, 2024 10:45AM in Oracle Analytics Cloud and Server

Hello Oracle Analytics Community,

I am currently working on replicating a Power BI visualization in Oracle Analytics Cloud (OAC) and I need assistance with translating a DAX calculation into OAC's calculation logic.

Objective:I am trying to replicate a bar chart and table visualization that shows the variance in scores between 2022 and 2021 for each sector.

Details:In Power BI, I have the following DAX calculation that calculates the variance score:

Variance Score = 
    DIVIDE(
        CALCULATE(
            AVERAGE(esgscores[Total-Score]),
            FILTER(Financial_Statements, Financial_Statements[year] = 2022)
        ) - CALCULATE(
            AVERAGE(esgscores[Total-Score]),
            FILTER(Financial_Statements, Financial_Statements[year] = 2021)
        ),
        CALCULATE(
            AVERAGE(esgscores[Total-Score]),
            FILTER(Financial_Statements, Financial_Statements[year] = 2021)
        )
    )

Explanation:

  • esgscores is a table containing the Total Score.
  • Financial_Statements is another table containing the Year column used for filtering.
  • The calculation computes the difference in average Total Score between 2022 and 2021, divided by the average Total Score for 2021.

    image.png

Question: How can I translate the above DAX calculation into Oracle Analytics Cloud (OAC) using the available calculation methods? Specifically, how do I replicate the CALCULATE, FILTER, AVERAGE, and DIVIDE functions in OAC?

Any guidance or examples would be greatly appreciated. Thank you!

Answers

  • [Deleted User]
    [Deleted User] Rank 7 - Analytics Coach
    edited Apr 10, 2024 4:21PM

    Take 3 steps back. Formulate what result you want to achieve with which base data. Then ask the question how you can achieve said result with said data.

    Tools are different. You can't translate concepts from one to the other 1:1.

    Also: You were in the OBIEE space. I've moved your post.

  • [Deleted User]
    [Deleted User] Rank 7 - Analytics Coach

    Dear nameless OP,

    Rejecting my input and then doing what I stated in the input is bad forum etiquette to say the least.

    Forums are places where people exchange and help each other. It's not a place where others "do your homework" for free and are then discarded. Please use the forum in a way which provides a good experience for all.

  • User_X8DWN
    User_X8DWN Rank 1 - Community Starter
    edited Apr 11, 2024 10:49AM

    Dear @Christian Berg,

    Thank you for your feedback and for highlighting the importance of constructive exchange within the forum community.

    I apologize if my initial query appeared to overlook your input. My intention in seeking assistance was to better understand the process and methodology for translating a specific DAX calculation into Oracle Analytics Cloud (OAC). I acknowledge the value of community support and am grateful for any guidance or resources shared.

    Regarding the approach I mentioned, I attempted to implement the calculation in OAC using the provided syntax.
    ((FILTER("esgscores"."Total-Score" USING "esgscores"."Year"='2022'))-((FILTER("esgscores"."Total-Score" USING "Financial_Statements"."year" = 2021))))/
    ((FILTER("esgscores"."Total-Score" USING "Financial_Statements"."year" = 2021)))

    Unfortunately, the outcome was not as expected, and I encountered difficulties achieving the desired result.

    If you have any additional insights, recommended resources, or guidance on how to effectively translate the DAX calculation to OAC, I would greatly appreciate your assistance. I have searched extensively online but have not found specific documentation related to this translation.

    Once again, I apologize for any misunderstanding, and I am committed to engaging constructively within the forum community.
    Thanks.