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

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

Accepted Solutions
Super User
Super User

Re: Calculate cumulative percentage from a calculated column

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

Super User
Super User

Re: Calculate cumulative percentage from a calculated column

@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
Super User
Super User

Re: Calculate cumulative percentage from a calculated column

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

erikah06 Regular Visitor
Regular Visitor

Re: Calculate cumulative percentage from a calculated column

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%
Super User
Super User

Re: Calculate cumulative percentage from a calculated column

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

Super User
Super User

Re: Calculate cumulative percentage from a calculated column

@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

erikah06 Regular Visitor
Regular Visitor

Re: Calculate cumulative percentage from a calculated column

This worked great @jdbuchanan71 Thank you! 

erikah06 Regular Visitor
Regular Visitor

Re: Calculate cumulative percentage from a calculated column

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 

Super User
Super User

Re: Calculate cumulative percentage from a calculated column

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

   

erikah06 Regular Visitor
Regular Visitor

Re: Calculate cumulative percentage from a calculated column

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

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 200 members 2,252 guests
Please welcome our newest community members: