Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Create Measure to Calculate Period over Period Margin Change

  • 1.  Create Measure to Calculate Period over Period Margin Change

    Bronze Contributor
    Posted Aug 13, 2019 10:12 AM
    HI All,

    I am currently building a report that measures the Margin Erosion period by period. i.e looking at what my profit margin last month was against what it is this month or even looking at Jan vs Feb.

    I have created measures for each month to find the margin. My problem is now i have created another measure(see below) that calculates the margin Erosion( DM Dollar change over periods) but its only comparing it to the Current DM(Today). I want to be able to create a measure that doesn't only compare prior months with today but can compare prior months against each other.

    MarginErosionTest = CALCULATE(SUMX(__POWERBI_ExecReport_POCMSR,__POWERBI_ExecReport_POCMSR[Current Direct Margin])-
    CALCULATE(
    SUMX(MSR_NoGroup,MSR_NoGroup[Updated DM]),
    FILTER(MSR_NoGroup,MAX(MSR_NoGroup[Sort]))))

    Here's an image of how my visual looks, right now if nothing is selected the Margin Erosion Measure just sums all the periods together but once i select a period it compares it against the "Current Direct Margin".

    PS: I know the Margin Erosion formula could be better so please i'm open to all suggestions

    Thanks in advance for any guidance in making this more dynamic!
    This shows the DM by Period but the Margin Erosion Test just sums them all


    ------------------------------
    Farida Adejare
    BI Analyst
    ------------------------------


  • 2.  RE: Create Measure to Calculate Period over Period Margin Change

    Gold Contributor
    Posted Aug 15, 2019 07:09 AM
    Hi @Farida Adejare,

    Your post doesn't make mention of the structure of your data model, so, I am wondering if you have a date table (marked as a date table) and if you have created relationships between those tables.
    Can you provide some information on that?

    At first view I would think a simple SUM() function could do, instead of CALCULATE() with embedded SUMX(). But I could be wrong, not knowing your model.
    Also, have you had a look at the "New quick measure" feature in Power BI?
    ​Click on the 3 dots beside the name of the table. It can be helpful in creating DAX formula.

    Let us know about your data model structure and we shall see how to help further. It could help if you share some sample data too.

    ------------------------------
    David Gerard
    Business Intelligence Consultant
    Kampala
    ------------------------------