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 )
tamerj1
Community Champion
Community ChampionHi Anonymous
Why the result for xyx is 20 not 15?
Because in jul there is only one sale and cone common sales, so the whole the cmmonsaale is considered in xyx.
- tamerj1
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 )