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
erikah06
Helper I
Helper I

Calculate cumulative percentage from a calculated column

Hello, 

 

I have a calculated column called [Availability] thanks to @AlB I am attempting to create a new column or measure, not sure which one makes more sense. The goal is to have a subsequent column/measure that sums the [Availability] column (%) of those items in the [STAT] column that meet certain criteria = "E0002", "E0003", "E0005", E00019" and sums those fields for each equipment and produces the measure/column called Operational. 

 

CountOBJNREquipmentYearTechnical obj. TypeSTATTXT30DIFPLANT NAMETech Obj Type NameClassAvailability
2IE0000000000100005751000057520011E0002Awaiting Acceptance12POLKNew Flyer 40'TRANSIT 
2IE0000000000100005751000057520011E0009Off Site Vendor29POLKNew Flyer 40'TRANSIT0.49%
2IE0000000000100005751000057520011E0010Off Site Leased0POLKNew Flyer 40'TRANSIT0.00%
1IE0000000000100005751000057520011E0014Non Operational Reserve Fleet4120POLKNew Flyer 40'TRANSIT69.42%
1IE0000000000100005751000057520011I0099Available5947POLKNew Flyer 40'TRANSIT 
7IE0000000000100005751000057520011E0019Operational Backorder241POLKNew Flyer 40'TRANSIT4.06%
6IE0000000000100005751000057520011E0005Operational Open Defect1158POLKNew Flyer 40'TRANSIT19.51%
10IE0000000000100005751000057520011E0008Off Site Central Shops99POLKNew Flyer 40'TRANSIT1.67%
24IE0000000000100005751000057520011E0013Non Operational Parts33POLKNew Flyer 40'TRANSIT0.56%
32IE0000000000100005751000057520011E0004Operational4375POLKNew Flyer 40'TRANSIT73.72%
2 ACCEPTED SOLUTIONS

I looks like it might be easiest to add a calculated column that captures operational or not.

 

Classification = IF ( LEFT ( Table1[TXT30] , 11 ) = "Operational" , "Operational" , Table1[TXT30] )

Then write a measure to sum the Availability

 

Total Availability = SUM( Table1[Availability] )

Then you can pull the Classification column and [Total Availability] into a report along with whatever other fields you need.

 

availability.jpg

View solution in original post

@erikah06

 

You seem to make contradictory statements. In the explanation you say:

If [STAT] ="E0019" or "E0005" then sum these fields with "E0004" else if [STAT] does not equal any of these designated [STAT] results then [Availability]

 

but then in the example you do the sum when [STAT] = "E0004" and leave blank the rows where  [STAT] ="E0019" or "E0005" (that is neither the sum nor [Availability]). 

I will assume that what you show in the example is what you really want. If so, try the following for the new calculated column:

 

Operational = 
IF (
    NOT Table1[STAT] IN { "E0019"; "E0005" };
    IF (
        Table1[STAT] = "E0004";
        Table1[Availability]
            + CALCULATE (
                DISTINCT ( Table1[Availability] );
                Table1[STAT] = "E0019";
                ALLEXCEPT ( Table1; Table1[OBJNR] )
            )
            + CALCULATE (
                DISTINCT ( Table1[Availability] );
                Table1[STAT] = "E0005";
                ALLEXCEPT ( Table1; Table1[OBJNR] )
            );
        Table1[Availability]
    )
)

If you don't want to return blank where [STAT] ="E0019" or "E0005" but [Availability] instead, you can use this (different from what you show on the example table): 

 

Operational2 =
IF (
    Table1[STAT] = "E0004";
    Table1[Availability]
        + CALCULATE (
            DISTINCT ( Table1[Availability] );
            Table1[STAT] = "E0019";
            ALLEXCEPT ( Table1; Table1[OBJNR] )
        )
        + CALCULATE (
            DISTINCT ( Table1[Availability] );
            Table1[STAT] = "E0005";
            ALLEXCEPT ( Table1; Table1[OBJNR] )
        );
    Table1[Availability]
)

Regarding whether a measure would be better, I would need to understand, conceptually, what you ultimately want to do with the data and what/how you want to show as result.

View solution in original post

8 REPLIES 8
AlB
Super User
Super User

Hi @erikah06

I'm afraid you'll have to explain a bit more what it is that you need and how you'd like to display it.

Can you provide an example based on your sample data, explaining how the [STAT] and [Equipment] fields would affect the result?

 

Cheers

Hi @AlB sorry for ambiguity. So, this is the hopeful outcome of the [Operational] field. If [STAT] ="E0019" or "E0005" then sum these fields with "E0004" else if [STAT] does not equal any of these designated [STAT] results then [Availability]

 

CountOBJNREquipmentYearTechnical obj. TypeSTATTXT30DIFPLANT NAMETech Obj Type NameClassAvailabilityOperational 
2IE0000000000100005751000057520011E0002Awaiting Acceptance12POLKNew Flyer 40'TRANSIT  
2IE0000000000100005751000057520011E0009Off Site Vendor29POLKNew Flyer 40'TRANSIT0.49%=0.49%
2IE0000000000100005751000057520011E0010Off Site Leased0POLKNew Flyer 40'TRANSIT0.00%=0.00%
1IE0000000000100005751000057520011E0014Non Operational Reserve Fleet4120POLKNew Flyer 40'TRANSIT69.42%=69.42%
1IE0000000000100005751000057520011I0099Available5947POLKNew Flyer 40'TRANSIT  
7IE0000000000100005751000057520011E0019Operational Backorder241POLKNew Flyer 40'TRANSIT4.06% 
6IE0000000000100005751000057520011E0005Operational Open Defect1158POLKNew Flyer 40'TRANSIT19.51% 
10IE0000000000100005751000057520011E0008Off Site Central Shops99POLKNew Flyer 40'TRANSIT1.67%=1.67%
24IE0000000000100005751000057520011E0013Non Operational Parts33POLKNew Flyer 40'TRANSIT0.56%=0.56%
32IE0000000000100005751000057520011E0004Operational4375POLKNew Flyer 40'TRANSIT73.72%=73.72%+19.51%+4.06%

I looks like it might be easiest to add a calculated column that captures operational or not.

 

Classification = IF ( LEFT ( Table1[TXT30] , 11 ) = "Operational" , "Operational" , Table1[TXT30] )

Then write a measure to sum the Availability

 

Total Availability = SUM( Table1[Availability] )

Then you can pull the Classification column and [Total Availability] into a report along with whatever other fields you need.

 

availability.jpg

This worked great @jdbuchanan71 Thank you! 

@erikah06

 

You seem to make contradictory statements. In the explanation you say:

If [STAT] ="E0019" or "E0005" then sum these fields with "E0004" else if [STAT] does not equal any of these designated [STAT] results then [Availability]

 

but then in the example you do the sum when [STAT] = "E0004" and leave blank the rows where  [STAT] ="E0019" or "E0005" (that is neither the sum nor [Availability]). 

I will assume that what you show in the example is what you really want. If so, try the following for the new calculated column:

 

Operational = 
IF (
    NOT Table1[STAT] IN { "E0019"; "E0005" };
    IF (
        Table1[STAT] = "E0004";
        Table1[Availability]
            + CALCULATE (
                DISTINCT ( Table1[Availability] );
                Table1[STAT] = "E0019";
                ALLEXCEPT ( Table1; Table1[OBJNR] )
            )
            + CALCULATE (
                DISTINCT ( Table1[Availability] );
                Table1[STAT] = "E0005";
                ALLEXCEPT ( Table1; Table1[OBJNR] )
            );
        Table1[Availability]
    )
)

If you don't want to return blank where [STAT] ="E0019" or "E0005" but [Availability] instead, you can use this (different from what you show on the example table): 

 

Operational2 =
IF (
    Table1[STAT] = "E0004";
    Table1[Availability]
        + CALCULATE (
            DISTINCT ( Table1[Availability] );
            Table1[STAT] = "E0019";
            ALLEXCEPT ( Table1; Table1[OBJNR] )
        )
        + CALCULATE (
            DISTINCT ( Table1[Availability] );
            Table1[STAT] = "E0005";
            ALLEXCEPT ( Table1; Table1[OBJNR] )
        );
    Table1[Availability]
)

Regarding whether a measure would be better, I would need to understand, conceptually, what you ultimately want to do with the data and what/how you want to show as result.

Again @AlB sorry for confusion, the first solution is what I was intending to relay. I needed to add 2 more [STAT] codes to your solution and viola, I had all 4 of the [STAT] codes necessary to portray an Operational status. Thank you. I already accepted @jdbuchanan71 as a Solution. I see you have been on here the longest out of the 3 of us @AlB, do you know if I can accept 2 solutions to the problem? Thank you 

@erikah06

Oh, no worries.

Sure, you can mark as many posts as you want as solutions, no problem. Just like you marked the first one, clicking on "Accept as solution". You can also "unmark" a solution if for instance you make a mistake identifying it. To do this, click on the ellipsis on the top right corner of the message and choose "Not the solution"

   

Thanks @AlB that was very helpful. You have a great day. Marked your response as a solution as well. 🙂

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.