Dax Measure value not showing

[nmaddock]

Hi,

I have a measure based on all the jobs and have removed the filters from the evaluation context so I am expecting the same value to be displayed, for example all agreements of industry type 'Cafe' should be displaying 91.15 for 'KG's per M3 by Industry' but some are blank but I need a value in each cell, how can I achieve this?

Many Thanks Nathan

https://www.dropbox.com/s/dqsg36arrimeqk7/Power%20BI%20Sample.pbix?dl=0

[SteveCampbell · Accepted solution]

You have this in your measure:

IF(ISBLANK([Weight by Volume]),

BLANK(),

CALCULATE([Weight by Volume],

The Wight by volume was blank for these rows. This was outside your calculate statement, so the all did not affect it.

Try and change your measure to this:

KG's Per M3 by Industry = 
    IF(ISBLANK([Total Annual Revenue]),
        BLANK(),
        CALCULATE([Weight by Volume], 
        ALL(User[Sales Person])
       ,ALL(Agreement[Contract Signed Date], Agreement[Customer Contract Start Date])
       ,ALL(Site[Account Code], Site[Company Name], Site[Site Name], Site[Site Town], Site[PostCode])
       ,ALL(MaterialTypes[Material Type])
       ,ALL(ContainerTypes[Container Type])
        ))

The blue comment you have on your picture, not sure if you're reffering to the lift rate column as the other one does add up. If so, this is because you use a "FIRSTNONBLANK". The "total" row in the matrix will calculate the measure without row context, not just sum of the rows. Therefore it will only calculate for the firstnonblank it finds.

Love hearing about Power BI tips, jobs and news?
I love to share about these - connect with me!

Stay up to date on  
Read my blogs on  

Remember to spread knowledge in the community when you can!