Skip to main content
To homepage To homepage
Solved

Analysis services tabular percentage calculation without empty values

  • January 22, 2025
  • 1 reply
  • 16 views
G
Contributor

I have a need to calculate a measure which would reflect the revenue growth over the year on different categories in percentage.

Currently i have a custom measure in semantic layer with following formula: Revenue Growth = ([Sales Revenue] / [Sales Revenue LY]) - 1

 

The categories in dimension which i have are Cat1, Cat2 (no data in the fact), Cat3. However if i try to show this on excel, it includes the Cat2 which has no records in the fact table, and also gives me an “empty” category which, i assume, is “summarization” like this:

Is there any way to make sure that the empty row and the category with no data in the fact would not appear?

Best answer by Christian Hauggaard

Hi ​@gediminas.juskenas 

I get the following results when using the following DAX calculation for revenue growth:

Revenue Growth = IF(
    AND(NOT(ISBLANK([Sales Revenue])), NOT(ISBLANK([Sales Revenue LY]))), 
    ([Sales Revenue] / [Sales Revenue LY]) - 1, 
    BLANK()
)

I do not get a Cat2 or blank category. Could you please try editing your measure to avoid doing the calculation when the value of sales revenue or sales revenue LY is blank?

View original
Did this topic help you find an answer to your question?

Christian Hauggaard
Community Manager
Forum|alt.badge.img+5

Hi ​@gediminas.juskenas 

I get the following results when using the following DAX calculation for revenue growth:

Revenue Growth = IF(
    AND(NOT(ISBLANK([Sales Revenue])), NOT(ISBLANK([Sales Revenue LY]))), 
    ([Sales Revenue] / [Sales Revenue LY]) - 1, 
    BLANK()
)

I do not get a Cat2 or blank category. Could you please try editing your measure to avoid doing the calculation when the value of sales revenue or sales revenue LY is blank?

Reply


Terms and ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings