Special column in pivot table - defect per unit calc

[xbr088]

Hi there.

I try to solve very hard issue (maybe hard only for me)

I try to dynamically calculate (in pivot table) parameter which i called defect per unit.

Lets imagine we have column A with various type of defects, column B with vehicle number..

If i would like to calculate defect per unit, it should be : count of all defect1 / all checked cars... in my case divide by 3 every time. And here is the issue.

Anyhow i add pivot to data model - anyhow i create special distinguis calc of vehicles (means unique), anyhow i add new measure in new column, i can only divide count of defect with related cars... But not all cars count as i need :-/

Please could you help me ?

Here are pictures and excel attached :

Screenshot_2023-03-28 15.36.45_thz9qu.png

Screenshot_2023-03-28 15.37.38_mpOWi2.png

[romperstomper]

Change the measure to:

=[Počet PROBLEM]/CALCULATE([Počet rôznych položiek v stĺpci VEHICLE],ALL('Tabuľka1'))

[xbr088]

My gosh ! It works ! i spent 4 hours for many tryouts, and you reply me in couple of minutes with functional solution.

Great, many thanks

[romperstomper]

Glad we could help.

[xbr088]

Glad we could help.

Only one additional question.

Is it possible to do also with pivot table which is not added to data model option ?

Because there is also option to add calculated field or item, but there is little different dialog box for some calculation

[romperstomper]

The calculations in calculated fields in a normal pivot table always sum the fields involved, and there is no distinct count option available, so you would have to do most of the work in the source data, or in Power Query for example, and then build your pivot table.