Failure with calculating subcategory sum

[Hyperchef1969]

Hi,

I am trying to calculate a value sum for a subcategory. There is a relationship on item ID from table OBSOLETE to table ITEMS. 

ART_GROUP_OBS_VALUE_NEW = CALCULATE(SUM(XXBI_OBSOLETE_ITEMS_V[GROSS_VALUE_NEW]),XXBI_ITEMS_MV[WEIR_OP_ARTICLE_GROUP_NAME]).

After this calculation I get an error message "Can't display the visual". Couldn't load the data for this visual. ORA-01722 invalid number: The exception was raised by the IDbCommand interface.

However changing the calculation to a specific subcategory (e.g. Frame) works, so I do not understand why it does not work for all subcategories.

This formula works!

ART_GROUP_VALUE_NEW = CALCULATE(SUM(XXBI_OBSOLETE_ITEMS_V[GROSS_VALUE_NEW]),XXBI_ITEMS_MV[WEIR_OP_ARTICLE_GROUP_NAME]="Frame")

Regards and thanks for your help!

[v-shex-msft · Accepted solution]

Hi @Hyperchef1969,

Maybe you can try to use following formula calculate summary value based on current subcategory:

ART_GROUP_VALUE_NEW =
CALCULATE (
    SUM ( XXBI_OBSOLETE_ITEMS_V[GROSS_VALUE_NEW] ),
    VALUES ( XXBI_ITEMS_MV[WEIR_OP_ARTICLE_GROUP_NAME] )
)

If above not help, can you please share some sample data for test?

Regards,

Xiaoxin Sheng

[PattemManohar]

@Hyperchef1969 Try to create as as measure if you want to see the total sum for each subcategory. Or if you want to store the total sum values for each subcategory then try to use SUMMARIZE function.

[Hyperchef1969]

Hi,

To my opinion this is a measure. Question is why it does not work for all subcategories but only for a specific subcategory such as "frame". Most helpful would be a calculation which can calculate total value for each subcategory.

[PattemManohar]

@Hyperchef1969 If it is a Measure you are looking for and all the relationships are in place, then use SUM(XXBI_OBSOLETE_ITEMS_V[GROSS_VALUE_NEW])