Forum Discussion
Measure inside a calculated column does not respect the filter
Hi All, I new to power bi so please bear with me in my query. I have the data similar to below, what i need is the commonsales is divided as per ratio to sales, but this should be month wise. S...
Anonymous
Here is a sample file with the solution https://www.dropbox.com/t/ZUwnQDxjU6jCf260You need first to create a Month-Year column that will be required in the calculation. Then create the required column measure as follows
Required Column = VAR CurrentSales = SUM ( 'Table'[Sales] ) VAR CurrentMonthTable = CALCULATETABLE ( 'Table', REMOVEFILTERS ( 'Table' ), VALUES ( 'Table'[Month-Year] ) ) VAR TotalCommonSales = SUMX ( CurrentMonthTable, 'Table'[Commonsales] ) VAR DatesWithSales = COUNTROWS ( FILTER ( CurrentMonthTable, 'Table'[Sales] > 0 ) ) VAR Ratio = DIVIDE ( CurrentSales, TotalCommonSales * DatesWithSales ) RETURN CurrentSales * ( 1 + Ratio )
Because in jul there is only one sale and cone common sales, so the whole the cmmonsaale is considered in xyx.
tamerj1
Community Champion
Community ChampionAnonymous
Here is a sample file with the solution https://www.dropbox.com/t/ZUwnQDxjU6jCf260
You need first to create a Month-Year column that will be required in the calculation. Then create the required column measure as follows
Required Column =
VAR CurrentSales = SUM ( 'Table'[Sales] )
VAR CurrentMonthTable = CALCULATETABLE ( 'Table', REMOVEFILTERS ( 'Table' ), VALUES ( 'Table'[Month-Year] ) )
VAR TotalCommonSales = SUMX ( CurrentMonthTable, 'Table'[Commonsales] )
VAR DatesWithSales = COUNTROWS ( FILTER ( CurrentMonthTable, 'Table'[Sales] > 0 ) )
VAR Ratio = DIVIDE ( CurrentSales, TotalCommonSales * DatesWithSales )
RETURN
CurrentSales * ( 1 + Ratio )