cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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.

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

1 ACCEPTED SOLUTION
Super User

@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 )``````
5 REPLIES 5
Resolver III

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

Super User

@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.

Super User

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

Anonymous
Not applicable

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.