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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vissvess
Helper V
Helper V

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
Anonymous
Not applicable

[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

Anonymous
Not applicable

-- 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

Dear @Anonymous ,

 

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

Anonymous
Not applicable

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

Hi @Anonymous ,

 

Here is the situation I am facing explained in detail.

Both the measures works fine as below.


New Measures OK - Expected resultNew 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.

My scenario - OK condition before new measuresMy scenario - OK condition before new measures

 

 

 

 

 

 

 

 

 

 

 

 

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

My scenario - Unexpected results addedupMy 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

@Anonymous , Kindly help.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors