cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Cumulative Total with two columns

Hello Everyone,

 

I'm dealing with following scenario.

We have table with following structure:

 

d8821daff2

 

What I would like to do is:

Calculate accumulative total for POS_AMT measure based on WEEK_END_DATE.

I did that using following formula:

POS_AMT_CUMULATIVE = 
CALCULATE(
	SUM(ARD_POS_FRCST_WKLY[POS_AMT]);
	FILTER(
		ALLEXCEPT(ARD_POS_FRCST_WKLY; ARD_PRODUCT_DIM[PRODUCT_SECTOR_NAME];ARD_PRODUCT_DIM[PRODUCT_CATEGORY_NAME]);
		ARD_POS_FRCST_WKLY[WEEK_END_DATE] <= MAX(ARD_POS_FRCST_WKLY[WEEK_END_DATE])
	)
)

However, at certain point, POS_AMT values for few weeks are Blanks, with just FORECAST_AMT column being filed.

 

0463ec1c94

 

What I would want to achieve is -> For WEEK_END_DATE values that have FORECAST_AMT, but doesn't have POS_AMT use FORECAST_AMT insead of POS_AMT in cumulative calculation.

 

The other way would be to calculate cumulative POS_AMT for only those weeks that have such values and later on calculate another measure with FORECAST_AMT, but only for those weeks that doesn't have POS_AMT.

 

Hope it's clear enough... 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

@Glaeran

 

Hi,

This formula can mark the first week that starts using FORECAST_AMT. Maybe you need to make some changes. It's used as calculated column. Hope this would help.

Column =
IF (
    ARD_POS_FRCST_WKLY[WEEK_END_DATE]
        = CALCULATE (
            MIN ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] ),
            FILTER ( ARD_POS_FRCST_WKLY, ARD_POS_FRCST_WKLY[POS_AMT] = 0 )
        ),
    1,
    0
)

 

Best Regards!

Dale

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

View solution in original post

5 REPLIES 5
Highlighted
Microsoft
Microsoft

@Glaeran

 

Hi, 

You just need to change SUM into SUMX. Try this one below.

POS_AMT_CUMULATIVE =

CALCULATE (

    SUMX (

        ARD_POS_FRCST_WKLY,

        IF (

            ARD_POS_FRCST_WKLY[POS_AMT] = 0,

            ARD_POS_FRCST_WKLY[FORECAST_AMT],

            ARD_POS_FRCST_WKLY[POS_AMT]

        )

    ),

    FILTER (

        ALLEXCEPT (

            ARD_POS_FRCST_WKLY,

            ARD_PRODUCT_DIM[PRODUCT_SECTOR_NAME],

            ARD_PRODUCT_DIM[PRODUCT_CATEGORY_NAME]

        ),

        ARD_POS_FRCST_WKLY[WEEK_END_DATE] <= MAX ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] )

    )

)

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

Thank you!

 

That actually is working almost as expected.

"Almost", because our "ideal" scenario, would be to have those values that are using [FORECAST_AMT} column to be colored separately. Is it doable though?

Highlighted

@Glaeran

 

Hi,

 

We can’t color them separately due to we accumulate the total. If the first  month uses FROCAST_AMT, we can say the last month uses it too. If we want to find out the current month using it or not, we can add a calculated column, and then add it to legend. This doesn’t work with area chart. You can have a look at the image below. The black column means this column uses FROCAST_AMT. Is this what you want?

 

UsingFrocast =

CALCULATE (

    IF ( MIN ( ARD_POS_FRCST_WKLY[POS_AMT] ) = 0, 1, 0 ),

    FILTER (

        ARD_POS_FRCST_WKLY,

        MONTH ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] )

            = MONTH ( EARLIER ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] ) )

    )

)


Cumulative Total with two columns .jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best regards

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

@v-jiascu-msftUnfortunately not :(.

 

Our goal is to show both accumulative POS_AMT and FORECAST_AMT on 1 chart (preferably Area Chart or Line Chart), with the same idea as before so:

 

If WEEK_END_DATE has POS_AMT show POS_AMT otherwise show FORECAST_AMT, or by any chance "mark" the week in which we're starting to use FORECAST_AMT.

Highlighted

@Glaeran

 

Hi,

This formula can mark the first week that starts using FORECAST_AMT. Maybe you need to make some changes. It's used as calculated column. Hope this would help.

Column =
IF (
    ARD_POS_FRCST_WKLY[WEEK_END_DATE]
        = CALCULATE (
            MIN ( ARD_POS_FRCST_WKLY[WEEK_END_DATE] ),
            FILTER ( ARD_POS_FRCST_WKLY, ARD_POS_FRCST_WKLY[POS_AMT] = 0 )
        ),
    1,
    0
)

 

Best Regards!

Dale

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors