Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    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 )
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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.