cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vissvess Member
Member

Cumulative total from top based on another column

Hi all,

 

I have a data similar to this one.

 

IDDescGroup
6628ASSY,CHAS,L6,7Chassis
00J0ASSY,CHAS,L6,SChassis
09FTASSY,CHAS,L6,5Chassis
0CHJASSY,CHAS,VDT,Chassis
0GD2ASSY,CHAS,7760Chassis
0PD2ASSY,FSD,MSDIGInternal Storage
0VCYASSY,CHAS,2.5,Chassis
0XWGASSY,CHAS,2.5XChassis
11R6ASSY,CHAS,VDT,Chassis
1P0XASSY,CHAS,L6,SChassis
1R3TASSY,CHAS,L6,3Chassis
1VRGRDIMMMemory
1W50ASSY,CHAS,4X3.Chassis
1Y0NASSY,CHAS,3.5XChassis
26KFASSY,CHAS,2.5XChassis

and so on.

 

I have this line of code for calculative cumulative percentage from top.

Cumulative % of Total = 
var __visibleConfigs = ALL('Config Table'[PPID])

var __totalQtyForVisibleConfigs =
    CALCULATE(
        [Quantity Produced],
        __visibleConfigs
    )
var __currentQty = [Quantity Produced]
var __visibleConfigsWithGreaterOrEqualQty =
    FILTER(
        __visibleConfigs,
        [Quantity Produced] >= __currentQty
    )
var __cumulativeQty =
    CALCULATE(
        [Quantity Produced],
        __visibleConfigsWithGreaterOrEqualQty
    )
var __cumulativeQtyPercent =
    if (
        ISEMPTY( __visibleConfigsWithGreaterOrEqualQty ),
        1,
        DIVIDE( __cumulativeQty, __totalQtyForVisibleConfigs )
    )
return
    __cumulativeQtyPercent

[Quantity Produced] = COUNTDISTINCT(Archive[Product ID]).

 

This thing works good on top of other filters which is fine for me.

 

But, I need another thing.

 

The cumulative percentage should be group specific however the filter on the group may be.

The cumulative total of least [Quantity Produced] commodity should be 100% for a particular group. Now considering the entire list, the value is not 100%.

 

Any suggestion to small correction in this code would be highly helpful.

 

Second requirement, a seperate measure is requested to display code instead of cumulative percentage based on its value. Say, x<80% as F, 80<x<95 as M & 95<x as R. This should also as requested earlier, to happen according to the group.

 

Thanks

6 REPLIES 6
Super User
Super User

Re: Cumulative total from top based on another column

-- I can't remember the whole setup, so I'm guessing
-- here a bit. Check if this works. If not, it's because
-- I can't see the model.

Cumulative % of Total =
// first, you have to know which groups are visible
var __visibleGroups = VALUES ( 'Config Table'[Group] )
// then you have to retrieve all PPID's for the visible
// groups, the rest goes as before
var __visibleConfigs =
	calculatetable(
		values ( 'Config Table'[PPID] ),
		__visibleGroups,
		all ( 'Config Table' )
	)
var __totalQtyForVisibleConfigs =
    CALCULATE(
        [Quantity Produced],
        __visibleConfigs
    )
var __currentQty = [Quantity Produced]
var __visibleConfigsWithGreaterOrEqualQty =
    FILTER(
        __visibleConfigs,
        [Quantity Produced] >= __currentQty
    )
var __cumulativeQty =
    CALCULATE(
        [Quantity Produced],
        __visibleConfigsWithGreaterOrEqualQty
    )
var __cumulativeQtyPercent =
    if (
        ISEMPTY( __visibleConfigsWithGreaterOrEqualQty ),
        1,
        DIVIDE( __cumulativeQty, __totalQtyForVisibleConfigs )
    )
return
    __cumulativeQtyPercent
Super User
Super User

Re: Cumulative total from top based on another column

[Character Code] =
// Bear in mind that percentages should always
// be calculated as numbers in between 0 and 1.
// If you want to show them as percentages, for
// instance 51%, then you should format the number
// as percentage instead of multiplying it by 100.
var __cummulPercent = [Cumulative % of Total] 
var __code =
	switch( true(),
		__cummulPercent > .95, "R",
		__cummulPercent > .80, "M",
		"F"
	)
return
	__code

Best

Darek

vissvess Member
Member

Re: Cumulative total from top based on another column

Dear @darlove ,

 

You are marvellous.

 

The code worked. But, there is a small issue.

 

When I add the measure to one of the table, the table entries gets added with all the ID irrespective of the scenario in which the table is in.

 

For ex. I had a table visualisation that list only one group value for a particular day. It had only 12 IDs with some other measures  in it. When I add this new FMR code to it, the table is filled with all the IDs and the corresponding FMR value. This is not intended. Where would be the error might be??

 

Whats your thought?

 

Thanks

Super User
Super User

Re: Cumulative total from top based on another column

Mate, since I can't see anything, I can't help you much. If the thing was working correctly before (apart from not being aware of groups), then you have to adjust this piece of code:

// first, you have to know which groups are visible
var __visibleGroups = VALUES ( 'Config Table'[Group] )
// then you have to retrieve all PPID's for the visible
// groups, the rest goes as before
var __visibleConfigs =
	calculatetable(
		values ( 'Config Table'[PPID] ),
		__visibleGroups,
		all ( 'Config Table' )
	)

because this is the only change. I don't know... Try to remove all ( 'Config Table' ) and see what happens.

 

Once again, I can't do anything because I can't see the model and understand the requirement. If you could paste some pics, that would certainly help.

 

Best

Darek

vissvess Member
Member

Re: Cumulative total from top based on another column

Hi @darlove ,

 

Here is the situation I am facing explained in detail.

Both the measures works fine as below.


Initial1.JPGNew Measures OK - Expected result

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

You can see the above image that both the new measures works in the right and expected way.

 

Here (below) I have my scenario, where there are date, tray size filters & commodity filter(applied on visual) and unit status filter (applied on visual). This is fine.

Initial2.JPGMy scenario - OK condition before new measures

 

 

 

 

 

 

 

 

 

 

 

 

But, when the FMR measure is introdued in the table, all the rows despite filters gets piles up.

Initial3.JPGMy scenario - Unexpected results addedup

 

 

 

 

 

 

 

 

 

 

 

 

The expected result would only be the code for the available rows.

 

Hope the condition is explained in the proper manner.

 

Much appreciation and thanks in advance for the solution and requesting a workaround to solve.

 

Thanks

Highlighted
vissvess Member
Member

Re: Cumulative total from top based on another column

@darlove , Kindly help.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 277 members 2,931 guests
Please welcome our newest community members: