Forum Discussion
Additional Measure to be defined
I have this below table for which, I need to determine the average value per Product category based on the country. I am able to get the values using the below measure . But I am getting stuck when I...
Hi Eyelyn,
Many Thanks for taking time out for the query. You need to use the formula that I have used above to determine the value as this will be rolling average. This is output that will come
what I am expecting is as per the next condition , since I have P1 and P2 in fornt of B respectively, the rolling average should reflect
the table i would prefer would be
I am unable to attach the PBix . One thing to note since for country US the Parameters are not marked as P1 & P2 the Rolling Avarge and Revised Value would be same.
Kindly check my above post all the measures are now given there. Pasting below for you benefit.
MAX-Year = MAX('Table'[Year])Criteria = VAR categoryNumber = If('Table'[Sub - Category]="E1"||'Table'[Sub - Category]="E2"||'Table'[Sub - Category]="E3","-",VALUE(RIGHT('Table'[Sub - Category],LEN('Table'[Sub - Category])-1)))RETURNSWITCH(TRUE(),categoryNumber<=4, "C1",categoryNumber<=8, "C2",categoryNumber<=12,"C3",BLANK())Rolling Global Average = Var tble= SUMMARIZE(FILTER('Table','Table'[Year]='Table'[MAX-Year]),'Table'[Country],"AVG",SUMX(VALUES('Table'[Product Name]),AVERAGE('Table'[Value])))Return AVERAGEX(tble,[AVG])
Additional Measures used:-
MAX-Year = MAX('Table'[Year])Criteria = VAR categoryNumber = If('Table'[Sub - Category]="E1"||'Table'[Sub - Category]="E2"||'Table'[Sub - Category]="E3","-",VALUE(RIGHT('Table'[Sub - Category],LEN('Table'[Sub - Category])-1)))RETURNSWITCH(TRUE(),categoryNumber<=4, "C1",categoryNumber<=8, "C2",categoryNumber<=12,"C3",BLANK())Rolling Global Average = Var tble= SUMMARIZE(FILTER('Table','Table'[Year]='Table'[MAX-Year]),'Table'[Country],"AVG",SUMX(VALUES('Table'[Product Name]),AVERAGE('Table'[Value])))Return AVERAGEX(tble,[AVG])Since US data is to heavy to load you can use India and change it to US and in parameter section make it No.
