Grouping if statements for a waterfall chart

[Abegi]

Good day,

I have been struggling with the following, hopefully the community can help me out.

To explain it simple, I have a dataset which consists of:
Account ID (unique ID),
Country,
Account Group name (Name of group in which Account ID belongs),
Sales,
Sales period (ex. Jan 2022)

I am using a waterfall chart which should show on a group level in which category they belong, Lost, Less, More, or new Sales, this is based on the months selected in the slicer (ex. Jan & Feb 2022). The problem is that they are all combined as "More" as you can see in the image.

In order to reach this, I had to create the following measures:

Revenue = SUM(Revenue)

PastYearRevenue =

CALCULATE(

Revenue,

PARALLELPERIOD(

'Master Calendar'[Date],

-12,

MONTH)

)

RevenueChange = Revenue - PreviousYearRevenue

Lost/Less/More/New should be defined on the following (on a group level):
if(and(Revenue =<0, PreviousYearRevenue >0),"Lost",
if(and(Revenue >0, PreviousYearRevenue > Revenue), "Less",
if(and(PreviousYearRevenue >0,Revenue > PreviousYearRevenue), "More",
if(and(Revenue >0, PreviousYearRevenue =<0), "New"

So there should be 4 new measures (Lost,Less,More,New) which should be filled in the waterfall chart accordingly.
However the problem is that the groups are all grouped as "More" as a whole sinds they are not defined on a group level.

Does anyone have a trick for this? I tried SUMMARIZE() but unsuccessfully.

Thank you in advance.

[v-yiruan-msft]

Hi @Abegi ,

How did you create these measures Lost,Less,More and New in your side? Does it exist the field Group with value "Lost","Less","More" and "New"? Could you please provide some sample data which involved in waterfall visual? It is better if you can share a simplified pbix file without sensitive info. Thank you.

Best Regards

[Abegi]

Thank you for replying @v-yiruan-msft .

That is what I am looking for to be honest. For now it is a simple formula (which is not correct):

More =

if([NMLL]="More",[Revenue change],0)

The results in this case are adding everything to "More" as you can see in the chart above since it takes all rows into account, and then defines it.

However NMLL as measure should be categorized distinctively on parent level, so they are added up in the right measure.

So I am looking for something that can first group the rows on parent level, then categorize if it is Lost, Less, More, New compared to last year. And then it should add them up.

To make it easier, the structure looks like this:

Parent A should be 30 more since (100-70=30) is more.

Parent B should be 60 less since (50-110=-60) is less.

Measure Lost/Less/More/New should add them all up somehow.

Let me know if this is enough information, I can try to create a sample .pbix, however this one has many datasets attached to sample it in a short time.