Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Samlebijoutier
Frequent Visitor

Calculate running total and sum up results only if value >0

Hello all,

 

I have a problem and i can't find any solution.

 

I have one running total measure (this one is ok):

 

_YTD CASH 512 = IF (
    MIN ('Calendar'[Date] )
        <= CALCULATE ( MAX ( 'G_L Entry'[Posting Date]); ALL ('G_L Entry') );
CALCULATE (SUM('G_L Entry'[Amount]);
        FILTER('G_L Account Category';'G_L Account Category'[Description]="Cash");
        FILTER (
            ALL ('Calendar'[Date]  );
           'Calendar'[Date]  <= MAX('Calendar'[Date])
        )))

 

And another measure that I can't find out how to sum up the way i want (this one below is wrong) :

_YTD CASH 512<0 test2 = IF([_YTD CASH 512]<0;0;[_YTD CASH 512])

 

I would like the measure to show results only when sum of running total is > to 0 but calculated by [No].

 

In other words, the measure must calculate running total normaly but when Running Total for a date < 0, then value for this date =0. This should be calculated independently for each G_L Account [No_] and then sum all of the value (see below)

 

Additionnal info : G_L Account [No_] is subcategory of G_L Account Category'[Description]="Cash"

 

help.png

 

Let me know if i'm not clear (I feel i'm not....)

And i thanks A LOT to the people who are kind to help...

 

7 REPLIES 7
dax
Community Support
Community Support

Hi Samlebijoutier,

Below is my design, I don't know your sample, below is my sample

idmonthstaamount

1 1 52000 10
1 2 52000 0
1 3 52000 100
1 4 52000 200
1 1 51000 -1100
1 2 51000 100
1 3 51000 -10
1 4 51000 20
2 1 52000 20
2 2 52000 0
2 3 52000 0
2 4 52000 10
2 1 51000 0
2 2 51000 -10
2 3 51000 -10
2 4 51000 30

I saw you calculate sum based on another measure, here I use a simple meausre,Measure 2 = SUM('Table'[amount]), you could replace this by your measure(_YTD CASH 512) to see whether it works or not 

Then I create another measure 

Measure 3 =
SUMX (
    FILTER (
        SUMMARIZE (
            'Table',
            'Table'[id],
            'Table'[month],
            'Table'[sta],
            "tempsum", IF ( [Measure 2] > 0, [Measure 2], 0 )
        ),
        'Table'[id] = MIN ( 'Table'[id] )
            && 'Table'[month] = MIN ( 'Table'[month] )
    ),
    [tempsum]
)
Measure 4 =
IF (
    HASONEVALUE ( 'Table'[month] ),
    [Measure 3],
    SUMX ( ( 'Table' ), [Measure 3] )
)

Then use measure4 in table like below

218.PNG

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello Zoe, 

 

Sorry but it does not work.

I can't find a solution.

Anybody help is welcome.

Anonymous
Not applicable

Might be worth providing your report/data set so people can have a play with it. Obviously, remove any confidential information/data.

Hello @Anonymous @dax ,

Not so easy to provide a sample without confidential data.

You can download the sample and maybe find out a solution.

Sample

 

It may be obvious...

 

Thanks to the community.

Hi Samleijoutier,

Based on your sample, you could try below measures(I create a column in entry

month = MONTH(entry[Posting Date])

)

Measure 2 =
SUMX (
    SUMMARIZE (
        entry,
        entry[G_L Account No_],
        "tempsum", IF ( [_YTD CASH 512] > 0, [_YTD CASH 512], 0 )
    ),
    [tempsum]
)
Measure 3 =
IF (
    HASONEVALUE ( entry[month] ),
    [Measure 2],
    SUMX (
        SUMMARIZE ( entry, entry[G_L Account No_], entry[month], "a", [Measure 2] ),
        [a]
    )
)

256.PNG

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

hello all, @Anonymous , @dax 

 

Thank you Zoe but i can't apply your solution to my datas. I unfortunately made a mistake when i tried to simplify the dataset and remove confidential information. The Running total Measure was wrong.

 

The good sample is here (i kept your measures of your last answer)

 

I've added some explanation inside.

 

Thank a lot.

Hello @dax , @Anonymous 

 

Please can someone help on this ?

 

Thank you a lot.

 

Sam

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors