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.

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 🙂 

DepartmentDateSalesCommonsalesRequired Column
xyx1-Jul-2210020
xyz10-Jul-220100
abc1-Aug-2210015
abx10-Aug-220100
vvv15-Aug-2210015

 

Any help in this regard will be highly appreciated.

 

Thanks

Anuj Priyadarshi

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 )

5 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        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.

  • saravanan_p's avatar
    saravanan_p
    Icon for Resolver III rankResolver III
    4 years ago

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