CODEWITHSUNDEEP
powerbidax
Mike
Mike

Reputation: 1

Power BI (DAX) - Month over Month Decliners Calculation

Working in Power BI, I've created a couple of nested measures used to calculate month over month growth.

MoM $ Adj CM-1 = CALCULATE ([MoM $],
    ALL ('Calendar'),
    'Calendar'[Relative Month] = "CM-1",
    'Group'[Flag] = "Yes",
    'Type'[Type] = "N/A"
)

This works as expected. I am trying to create another measure based on the MoM $ Adj CM-1 measure to calculate the sum of only the negative values so I can see the top declining accounts.

Top Decliners = = CALCULATE ([MoM $],
    ALL ('Calendar'),
    'Calendar'[Relative Month] = "CM-1",
    'Group'[Flag] = "Yes",
    'Type'[Type] = "N/A",
    [MoM $] < 0
)

This doesn't work (throws an error).

Question is it possible to use the existing measure with an additional filter to sum only the negative values so I can keep all existing filters? Or do I need to write the measure from the base table and reapply all the filters?

Upvotes: 0

Views: 360

Answers (1)

alejandro_hagan
alejandro_hagan

Reputation: 1003

It is really hard to work on powerbi measures in stack overflow because we can't reproduce your work.

However, I believe you are trying to reference a measure in calculate which normally isn't allowed with out a filter wrapper.

Rather than trying to incorporate filter in your answer, may I suggest you create a new "root" measure of negative only MoM measure and then reference that measure in the same way as your first formula?

If you want to use filter then try the below. Depending on the base measure is calculated, you need to validate if the results are correct:

Top Decliners = CALCULATE ([MoM $],filter(
    ALL ('Calendar'),
    'Calendar'[Relative Month] = "CM-1",
    'Group'[Flag] = "Yes",
    'Type'[Type] = "N/A",
    [MoM $] < 0)
)

Upvotes: 1

Related Questions