Measure inside a calculated column does not respect the filter

[Anonymous]

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.

So for this I created a measure as ratio=CALCULATE(DIVIDE(SUM(Table[Sales],Table[Commonsales]))*100, Allselected(Table, Table[Date])) This works fine but when i use it in calculation in the "Required Column" as:

Sales + Sales * ratio 

It does not respect the filter. I understand columns are precalculated and that is why this happens.

But the problem is i cannot use measure for "required column" becuase both sales and commonsales are calculated column. So it seems like a deadlock 🙂 

Department	Date	Sales	Commonsales	Required Column
xyx	1-Jul-22	10	0	20
xyz	10-Jul-22	0	10	0
abc	1-Aug-22	10	0	15
abx	10-Aug-22	0	10	0
vvv	15-Aug-22	10	0	15

Any help in this regard will be highly appreciated.

Thanks

Anuj Priyadarshi

[saravanan_p]

The measure you mentioned as ratio isnt working first of all. Post the desired result so that we could help you 

[amitchandak]

@Anonymous , Please check the update from @tamerj1 

A calculated column will not table slicer filter , so you have to create a meausre only

Try like

sumx(Table, [Sales] + [Sales] * [ratio]) 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[tamerj1]

Hi @Anonymous 
Why the result for xyx is 20 not 15?

[Anonymous]

Because in jul there is only one sale and cone common sales, so the whole the cmmonsaale is considered in xyx. 

[tamerj1 · Accepted solution]

@Anonymous 
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 )