Forum Discussion

shubh_kush's avatar
shubh_kush
Helper I
5 years ago

How to use Calculated column values with measures ?

Hi all,

I have a column Cost_Percentage and a measure Base_Percentage. DAX for Base_Percentage is as follows:

Base_Percentage =  IF(HASONEVALUE('Table_A'[Percentage_Base_Change]), VALUES('Table_A'[Percentage_Base_Change]), 0)

I am using this Base_Percentage measure in slicer so that user can select the specific percentage. 

 

I want to subtract Cost_Percentage and Base_Percentage (Whatever user selects).

Say if user selects Base_Percentage = 30 from the slicer,  then values in the column Cost_Percentage should be subtracted by 30.

But I cannot use Cost_Percentage with Base_Percentage in the measure directly .

 

Say, Measure_1 = (Cost_Percentage - Base_Percentage),  How can I read all single rows values of the column Cost_Percentage and subtract it from the measure Base_Percentage for which the user will select the value from the slicer?

The logic is quite large but for now I just want to know how can I use the column in the measure for some mathematical expression.

 

Thanks in Advance.

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    shubh_kush , You can not create a column with measure. You have to create measure and force context at like level

     

    new measure

    sumx(values(Table[ID]) , sum(Table[Cost_Percentage]) -[Base_Percentage])

    Assuming ID is at the lowest level.

     

    if not then try like

    sumx(values(Table[ID]) , sum(Table[Cost_Percentage]) -[Base_Percentage]*count(Table[ID]))