Forum Discussion
LOD Fixed Calculation - DAX help
We have sales table contains the column with this hierarchy like (GPB, Account, parent account, sales rep, product Id's, Sales, Qty). We have requirement like fixed level(GPB Level) calculation to display the Avg Selling Price (GPB_ASP=Sales/Qty). When we adding the transaction's data until product Id's in table visual it has to show GPB Level summarized ASP (GPB, Account, parent account, sales rep, product Id's, Sales, Qty, GPB_ASP). For that i have created the below measure result giving correctly
but the problem when i'm filtering the different column other than GPB (Account, Product Id etc). the GPB ASP value is not changing based on filter selection. Fixed GPB_ASP value has to change based on filter value .
Can you help me on this.
GPB_ ASP = ---------------------------------------------------------
VAR _selectedyr =
SELECTEDVALUE('Date'[Year Number])
VAR _selectedqtr =
VALUES('Date'[YYYY/QQ])
-----------------------------
//new date table
VAR DateTable =
CALCULATETABLE(
'Date',
ALL( 'Date' ),'Date'[Year Number]=_selectedyr,'Date'[YYYY/QQ] IN _selectedqtr
)
---------------------------------------------------------
VAR from_salesamount =
CALCULATE( [Sales Amount],DateTable,ALLEXCEPT(Sales,Sales[GPB]))
---------------------------------------------------------
VAR from_qty =
CALCULATE( SUM(Sales[Item Qty]),DateTable,ALLEXCEPT(Sales,Sales[GPB]))
RETURN IFERROR(from_salesamount/from_qty,0)