Unexpected measure calculation for period with no data.

[erdinch]

Hello,

I have a graph that is showing measure  calculated in that way:

Measure=100-100*sum(colimnA)/sum(columnB)

my first graph represents data in quarters and I have data only for Jan and Feb and it looks like:

 

Later on I decided to do some simplification of source data.

Did the same graph but on different page. So I created the same formula in the new query, did the new relationship...

Unfortunately the 2nd graph looks like:

So the new graph is showing measure result of 100 for Q2, Q3, Q4 for period where I do not have any data.

If we look formula again:

Measure= 100-100*sum(colimnA)/sum(columnB)-I guess that the part 100*sum(colimnA)/sum(columnB) is equal to 0 so

100-0=100.

My question is:

what I did differently in the first graph so measure calculation for period with no data is NOT reflected in the graph.

I need to transform second graph in the way how 1st graph is showing data.

Thanks!!!

[Omega]

Hi, 

Try the following measure: 

Measure = 
var x = Divide (Sum (column A), Sum (column B),0)
var y = 100 - (100*x)
return
if ((isblank (column A) && isblank (column B)) || x=0, Blank () , y)

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks.

My Recent Blog -
Time Intelligence - Direct Query

[erdinch]

Here is a URL link LINK 

[Anonymous]

Hi @erdinch ,

If it is possible to upload your PBIX file to OneDrive or other platform, and provide us the shared file link? Thank you.

Best Regards

Rena

[erdinch]

Hello,

I upload the file to my drive here is the LINK

[Anonymous]

Hi @erdinch,

Whether your problem has been resolved?

Best Regards

Rena

[erdinch]

Hello,

I have just tested it over the weekend. Works fine!

Thanks.

[Anonymous · Accepted solution]

Hi @erdinch,

You can make a minor adjustment on the formula of the two measures %MSA and %MSA+ which you drag on the visuals: add a judgment condition. If SUM ( [ABS (Plan - Actual),cs] ) / SUM ( [Plan Prod Qty,cs] ) equal 0, return BLANK () (as shown in below table). Then please check if it can get the right results...

%MSA =

IF (

   SUM ( [ABS (Plan - Actual)_] ) / SUM ( [Plan Prod Qty_] ) = 0,

   BLANK (),

   100

       - 100 * SUM ( [ABS (Plan - Actual)_] )

           / SUM ( [Plan Prod Qty_] )

)

%MSA+ =

IF (

   SUM ( [ABS (Plan - Actual),cs] ) / SUM ( [Plan Prod Qty,cs] ) = 0,

   BLANK (),

   100

       - 100 * SUM ( [ABS (Plan - Actual),cs] )

           / SUM ( [Plan Prod Qty,cs] )

)

Best Regards

Rena