Dividing two columns after they have been filtered

[Crav888]

Hi,

I have looked around the forum and I can't seem to find a satisfying answer on my issue.

What I want to do is to visualise my data in a table or something similar:

I want to see the following:

Plant             Cost Type name                  Difference between WOD and CCC in Internal and Spare parts

Plant a        (Internal + spare parts)         (Sum of WOD/sum of CCC)

Plant b        (Internal + spare parts)         (Sum of WOD/ sum of CCC)

Plant c        (Internal + spare parts)         (Sum of WOD/ sum of CCC)

My data looks is somewhat similar to the example below:

Plant             Cost Type name                                CCC                         WOD

Plant a            Internal                                            10                                 2

Plant a             External                                           2                                   12

Plant b             Internal                                           1                                    1

Plant b             External                                          4                                     4

Plant b            Spare parts                                     7                                     10

Plant a            Internal                                            4                                    5

Plant a             External                                           3                                   3

Plant b             Internal                                           8                                   9

Plant b             External                                          19                                    5

Plant b            Spare parts                                      3                                    12

Plant c             Internal                                           8                                   2

Plant c             External                                          4                                    5

Plant c            Spare parts                                      3                                    12

This is my first post to Power Bi community, so I apologize for the lack of correct lingo and visualisation.

I look forward to your answers 🙂

[v-shex-msft]

Hi @Crav888,

Actually you not need to add an external filter on this formula, power bi will auto filter the measure to calculate on current filtered records.

Result = SUM('sample'[WOD])/SUM('sample'[CCC])

BTW, when your calculation will break current filters, then you need to add some additional filter in the formula.

For example: I want to know the percent of current selected and unfiltered records.

Pecent of current Plant = 
var currentPlant=LASTNONBLANK('sample'[Plant],[Plant])
Return
SUM('sample'[WOD])/SUMX(FILTER(ALL('sample'),[Plant]=currentPlant),[WOD])

Regards,

Xiaoxin Sheng

[Crav888]

Thank you so much for your help. I can follow some of the logic 🙂

However, I want the table to give one result for Plant A, one result for Plant B etc.

Plant A: (Summarized Internal WOD + summarized spare parts WOD) / (Summarized Internal CCC + summarized spare parts CC)

Plant B: (Summarized Internal WOD + summarized spare parts WOD) / (Summarized Internal CCC + summarized spare parts CC)

Furthermore, I get the same result for all the plants. Not sure how to fix it.

I want something like this:

However, the two columns do not match with the result in the column :S

[Crav888]

That is so great.

One last thing - I think.

Now I have two Plant A001 rows. I just want one Plant A001 that shows external cost and spare part cost for WOD and divides that with external cost and spare part cost for CC

Is thas possible?

[v-shex-msft · Accepted solution]

Hi @Crav888,

>>Now I have two Plant A001 rows. I just want one Plant A001 that shows external cost and spare part cost for WOD and divides that with external cost and spare part cost for CC

Remove the 'cost type name' column from the visual and you will get the summary result which you wanted.

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @Crav888,

My formula is measure, if you direct put it as calculated column, it will get the static value of all table.

Regards,

Xiaoxin Sheng