Dax measure to calculate a formula with blanks values

[Adelphe]

Hello ,

i am a beginner in dax and i need help , with the following table 

i would like to write a dax query to calculate the Result.
in DAX i have tried the following :

when i summarize , i am loosing SAM rows because of Blanks values , can i keep those blanks and have the right result.

[dharmendars007 · Accepted solution]

Hello @Adelphe , 

Please use this formula to create a calculated column, to get the "0" included in your table.

Result =
VAR CurrentMonth = [Month]
VAR TotalLevelPerMonth = CALCULATE(SUM('Table'[Level]),ALLEXCEPT('Table', 'Table'[Month]))
RETURN
IF(TotalLevelPerMonth = 0, BLANK(), [Experience] * [Level] / TotalLevelPerMonth)

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[Anonymous · Accepted solution]

Hi @Adelphe ,

Thanks for @dharmendars007 reply, that's really helpful!

@Adelphe Here is another method for your reference.

SUMMARIZECOLUMNS('Table'[Username],'Table'[Year],'Table'[Month],'Table'[Level],'Table'[Experience],"Result",IF(ISBLANK(MAX('Table'[Experience])),0,MAX('Table'[Level])*MAX('Table'[Experience])/CALCULATE(SUM('Table'[Level]),FILTER(ALL('Table'),'Table'[Month]=MAX('Table'[Month])))))

The result of my test.

Best regards,

Mengmeng Li

[dharmendars007 · Accepted solution]

Hello @Adelphe , 

Please use this formula to create a calculated column, to get the "0" included in your table.

Result =
VAR CurrentMonth = [Month]
VAR TotalLevelPerMonth = CALCULATE(SUM('Table'[Level]),ALLEXCEPT('Table', 'Table'[Month]))
RETURN
IF(TotalLevelPerMonth = 0, BLANK(), [Experience] * [Level] / TotalLevelPerMonth)

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes 👍 are much appreciated!

Thank You

Dharmendar S

LinkedIN