Percentage difference - empty cells

[Fidzi8]

Hi,
I would need advice.

How to fix to get -3.27%? I don't want to count empty cells.

Thank you
Ondřej

[v-kelly-msft · Accepted solution]

Hi @Fidzi8 ,

Create a measure as below:

_Measure = 
var _diff=DIVIDE(CALCULATE(SUM('Table (2)'[Cost/tn 21])-SUM('Table (2)'[Cost/tn 20]),FILTER('Table (2)','Table (2)'[Category]=MAX('Table (2)'[Category]))),SUM('Table (2)'[Cost/tn 20]))
var _totalfor21=CALCULATE(SUM('Table (2)'[Cost/tn 21]),NOT(ISBLANK('Table (2)'[Cost/tn 20])),ALL('Table (2)'))
var _totalfor20=CALCULATE(SUM('Table (2)'[Cost/tn 20]),NOT(ISBLANK('Table (2)'[Cost/tn 21])),ALL('Table (2)'))
Return
IF(ISINSCOPE('Table (2)'[Category]),_diff,DIVIDE(_totalfor21-_totalfor20,_totalfor20))

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Fidzi8 , depend on formula, Share the formula and what are the group columns

Refer: https://www.youtube.com/watch?v=ufHOOLdi_jk