Adding dimension value changes the measure calculation

[leo_89]

Hi there,

The data model of my report look as follow:

The fact table contains information about the cancelled job which includes job id, cancel date, the reason for cancellation, and contractor who cancelled the job. The contractor and cancellation reason are the dimension tables.

I have a measure that calculates the percentage of cancelled job. Measure looks like as follow:

% of cancelled Jobs : [Cancelled job] / [Total Jobs]

Cancelled Job = calculate(count (Table[job_ID), not(isblank(Table[cancel_job_date])))

Total Jobs = DistinctCount(Table[job_ID)

I am using table visual to display the result. The result looks as follow:

It looks fine. The problem comes in when I include the cancellation reason into the table visual (as shown in the following picture):

As you can see the total jobs of connectics (which is 261) distributed w.r.t to cancellation reason which disturb the [% of the cancelled job] calculation. For example, the number of cancelled jobs due to Health & safety hazard is 10, so I want to see % of cancelled jobs for that reason should be 10/52 = 19.23%, not 100% (as shown in the picture). Could anyone help me where am I making the mistake?

A file can be download from here

[Rajesh]

Hi @leo_89

Create measure like below.

Sample6.pbix (1.8 MB)

[leo_89]

@Rajesh, Thanks for the reply. It looks fine. Apart from the scenario when I remove the cancellation reasons from the table, measure ([% of Cancelled]) show 100% for each contractor (which is not the case). For example, in picture it shows Connetics has 100% cancelled jobs whereas connectics has only 52 jobs cancelled out of 261 so % of cancelled should be 19.92%.

[Rajesh]

@leo_89

You can modify the measure to work for both the cases.

Sample6 (1).pbix (1.8 MB)

[EnterpriseDNA]

Hi @leo_89 , did the response provided by @Rajesh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!