Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Formula Dax -

Good morning, 

 

I am trying to create a new column ( example below" created " column is the result I would like to get ). The new column should copy the value of the " DEN" column for each KPI  ( taking into account the MARKET except for the KPI "NNS Aff"  where it should change the value instead of zero it should take the value of NNS for the appropriate market.

 

 

KPI                      time         Cluster    Sub cluster     Market      C       B. A                 num           num-calc     den          den-c   created 

NNS2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition5819,9135819,913000
Sales PFME2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition240,0538240,05385819,91305819,913
FFOH2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition698,0702698,07025819,91305819,913
FFOH Int2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition538,5705538,57055819,91305819,913
PRO2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition-145,042-145,0425819,91305819,913
PRO Int2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition-156,0896-156,08965819,91305819,913
Dep FF2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition78,023778,02375819,91305819,913
Dep FF Int2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition101,4998101,49985819,91305819,913
OPFE2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition26,238126,23815819,91305819,913
Bad Goods2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition6,62696,62695819,91305819,913
PC2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition2290,44452290,44455819,91305819,913
FDC2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition153,2392153,23925819,91305819,913
Structural Costs2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition1332,28861332,28865819,91305819,913
NNS Aff2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition2189,49622189,4962005819,913
OP1 Aff2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition183,6664183,66642189,496202189,4962
Volumes2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition616616000
Data Protection
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

    Please try below steps:

    1.my test table

    Table:

    2. add a new column by below dax formula

     

    Column =
VAR cur_kpi = 'Table'[KPI]
VAR cur_market = 'Table'[Market]
VAR cur_numcalc =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[Num-calc] ),
        'Table'[KPI] = "NNS",
        'Table'[Market] = cur_market,
        ALL ( 'Table' )
    )
RETURN
    IF ( cur_kpi = "NNS Aff", cur_numcalc, 'Table'[Den] )

     

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    Please try below steps:

    1.my test table

    Table:

    2. add a new column by below dax formula

     

    Column =
VAR cur_kpi = 'Table'[KPI]
VAR cur_market = 'Table'[Market]
VAR cur_numcalc =
    CALCULATE (
        SELECTEDVALUE ( 'Table'[Num-calc] ),
        'Table'[KPI] = "NNS",
        'Table'[Market] = cur_market,
        ALL ( 'Table' )
    )
RETURN
    IF ( cur_kpi = "NNS Aff", cur_numcalc, 'Table'[Den] )

     

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Anonymous Thanks a lot ! 

  • AntonioM's avatar
    AntonioM
    Solution Sage
    4 years ago

    Hi Anonymous,

     

    Your column headers have shifted around a bit so apologies if I've misunderstood but please give this a try.

     

    Created = IF ( 'Table'[KPI] = "NNS Aff" , CALCULATE ( SELECTEDVALUE ( 'Table'[num-calc] ) , ALLEXCEPT ( 'Table','Table'[Market] ) , 'Table'[KPI] = "NNS" ) , 'Table'[den] )

     

    For NNS Aff it finds the value of 'num-calc' where 'Market' is the same. For all other KPIs it just copies across 'den'

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    hi AntonioM ,

     

     I just tried and it is not working.

    For "NNS Aff" it should find the value of 'num-calc' of the corresponding " NNS " where 'market' are the same. For all other KPIs it just copies across 'den'.

     

     

     

    Thank you for your answer , I m still working on it. 

     

    Best regards, 

    A.