Calculation of the sum of different status and dividing by total value.

[Vaishakraju]

Hello everyone ,

I have a sharepoint list with different part numbers and status (Work in Progress, Finished , Packed). As mentioned below:

I need to caluculate the percentage of each Status value and show all the status percentage values in a graph.

But ineed to divide each Status value by a total( Say 40) Beacuase the total number of parts to be produced or the Work Order is 40 and i need to know the overall status. HOw can i do it?

For Ex. Count of Status  Finished =1, So total percentage = 1/40

Similarly,  Count of Status Packed = 2, Total%=2/40

Count of Status Packed= 2, Total%=2/40

The rest 35 Parts still needs to be worked Total%= 35/40

This data i need to show as a Graph

Part Number	Status
254_A	Finished
255_B	Packed
255_B	Packed
256_A	Work in Progress
257_A	Work in Progress

It would be great if you can help me out

Thanks

[Vaishakraju]

HI Dino @Anonymous @, Thankyou for the detailed solution.

In your case above there are 10 parts in the table,  so For finshed column it is 5/10= 50%.  its correct

But i need to calculate the percentage of the total  number of parts in a work order as i explained in my Question which is 40. So in my case it should show 5/40= 12.5% as the finished percentage value.

All the 40Parts may not be in the table but it has to consider the whole work order of 40parts

[Anonymous]

Hi @Vaishakraju ,

Are you trying to say that you have a total of 40 parts, but not all of those 40 exist in the datasheet? For example, there may be only 30 parts recorded in the table, but you still want to calculate the proportion based on the total number of 40, right?

If so, you can try to modify the three DAXs for calculating precentages to read as follows:

Finish percentage = DIVIDE([TotalFinish], 40)

Packed percentage = DIVIDE([TotalPacked], 40)

Work in Progress percentage = DIVIDE([TotalWork in Progress], 40)

And now, the final output is shown in the following figure:

If you change the total number of parts later, for example, say to 50 parts, you only need to change '40' of the three DAXs to '50'.

Best Regards,

Dino Tao

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

[Anonymous]

And if you mean that these 40 parts are all in the datasheet, but there are duplicates, such as in your original question 255_B which appears twice:

Because duplicate records affect the final calculation result, you can modify the DAX statement that calculates the the sum of different status as follows:

TotalFinish = CALCULATE(DISTINCTCOUNT('Table'[Part Number]),'Table'[Status] = "Finished")

The other two also change 'COUNT' into 'DISTINCTCOUNT'.

This eliminates the effect of duplicate columns.

[Anonymous]

Hi, @Vaishakraju ,

You can use the following DAX to calculate the sum of different status:

TotalFinish = CALCULATE(COUNT('Table'[Part Number]),'Table'[Status] = "Finished")

TotalPacked = CALCULATE(COUNT('Table'[Part Number]),'Table'[Status] = "Packed")

TotalWork in Progress = CALCULATE(COUNT('Table'[Part Number]),'Table'[Status] = "Work in Progress")

And you can use the following DAX to calculate each state as a percentage of the total:

Finish percentage = DIVIDE([TotalFinish], COUNT('Table'[Part Number]))

Packed percentage = DIVIDE([TotalPacked], COUNT('Table'[Part Number]))

Work in Progress percentage = DIVIDE([TotalWork in Progress], COUNT('Table'[Part Number]))

The final output is shown in the following figure:

Best Regards,

Dino Tao

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