Forum Discussion
Variation instead total in Matrix
gomezc73 - Put together a more concrete example for you with your data. The quick measure basically discusses the concept of how to do it, how to use HASONEVALUE, ISINSCOPE, etc. to find out where you are in the hierarchy/matrix and then you can decide how to do your calculation accordingly. See attached PBIX beneath sig. Page 6, Table (6), Measure 6.
Measure 6 = VAR __productcode = MAX([product code]) VAR __description = MAX([description]) VAR __year = MAX([year]) VAR __Month = MAX([Attribute]) VAR __2019 = SUMX(FILTER(ALL('Table (6)'),[product code]=__productcode && [description]=__description && [Attribute]=__Month && [year]=2019),[Value]) VAR __2020 = SUMX(FILTER(ALL('Table (6)'),[product code]=__productcode && [description]=__description && [Attribute]=__Month && [year]=2020),[Value]) RETURN IF(ISINSCOPE([year]),SUM([Value]),__2020 - __2019)
Thanks fot your help, It can help is very similar. but i am not an expert user.. Sorry!!. Is very complicated to modified the formula to generated the difference insted MIN/Max or AVE?
gomezc73 - Put together a more concrete example for you with your data. The quick measure basically discusses the concept of how to do it, how to use HASONEVALUE, ISINSCOPE, etc. to find out where you are in the hierarchy/matrix and then you can decide how to do your calculation accordingly. See attached PBIX beneath sig. Page 6, Table (6), Measure 6.
Measure 6 = VAR __productcode = MAX([product code]) VAR __description = MAX([description]) VAR __year = MAX([year]) VAR __Month = MAX([Attribute]) VAR __2019 = SUMX(FILTER(ALL('Table (6)'),[product code]=__productcode && [description]=__description && [Attribute]=__Month && [year]=2019),[Value]) VAR __2020 = SUMX(FILTER(ALL('Table (6)'),[product code]=__productcode && [description]=__description && [Attribute]=__Month && [year]=2020),[Value]) RETURN IF(ISINSCOPE([year]),SUM([Value]),__2020 - __2019)
