Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Store measure as constant

Hi! I want to do two things.

1. Calculate a fraction (percentInvoiced) based on two columns (Budget and Costs). This fraction is filtered by a date filter.

2.  Use this fraction to calculate the future relationship between the said columns in a new column in the same table (canInvoice). 

 

The problem is that the measure "percentInvoiced" calculates for each month/row in the table when in a table context. I want the measure to behave strictly like a constant (0.62 in this case), and populate the table with this number on all the rows. Then the "canInvoice"-column will be "budget" * "percentInvoiced".

 


I have tried different context filters and duplicating tables, but I run into some trouble in all the solutions I have tried.

 

Thanks!

Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    Is the date slicer at the top coming from a different table to the year & month in the matrix visual? If so, you should be able to calculate the overall fraction by using REMOVEFILTERS on all of the tables which are referenced in the matrix, and any table which they are linked to. So you could create a measure like

    can invoice =
var overallFraction = CALCULATE( [prosentFakturert],
REMOVEFILTERS('Sales'),
REMOVEFILTERS('Date')
)
return SUMX('Sales', 'Sales'[budget] * overallFraction)

1 Reply

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    4 years ago

    Is the date slicer at the top coming from a different table to the year & month in the matrix visual? If so, you should be able to calculate the overall fraction by using REMOVEFILTERS on all of the tables which are referenced in the matrix, and any table which they are linked to. So you could create a measure like

    can invoice =
var overallFraction = CALCULATE( [prosentFakturert],
REMOVEFILTERS('Sales'),
REMOVEFILTERS('Date')
)
return SUMX('Sales', 'Sales'[budget] * overallFraction)