Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Dax formula - Select value - all except

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.

There is many different markets so the formula should take the market into consideration.

Best regards,

A.

 

 

 

kpiTIMECLUSTERSUB CLusterMarketcategoryB. Areanumnum-calcdenden-calccreated
NNS2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition5819,9135819,913000
OthRev2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition005819,91305819,913
COGS2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition2223,92342223,92345819,91305819,913
Comm & OVE2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition12,883412,88345819,91305819,913
VDC 3P2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition216,1187216,11875819,91305819,913
TDC2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition5332,11785332,11785819,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 ClusterOceaniaPakistanMNMedical Nutrition61661600150
GPS2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition8839,12868839,128600150
OthRev2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition005819,9130150
COGS2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition2223,92342223,92345819,9130150
Comm & OVE2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition12,883412,88345819,9130150
VDC 3P2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition216,1187216,11875819,9130150
TDC2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition5332,11785332,11785819,9130150
Structural Costs2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition1332,28861332,28865819,9130150
NNS Aff2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition2189,49622189,496200150
NNS2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition150150000
Data Protection
Need Help
Show and Tell

5 Replies

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi Anonymous ,

     

    Can you explain why these rows get 150? As your description, they should copy the column den.

     

    Volumes 2021 YTD - 02 AOA Cluster Oceania Pakistan MN Medical Nutrition 616 616 0 0 150
    GPS 2021 YTD - 02 AOA Cluster Oceania Pakistan MN Medical Nutrition 8839,1286 8839,1286 0 0 150
    OthRev 2021 YTD - 02 AOA Cluster Oceania Pakistan MN Medical Nutrition 0 0 5819,913 0 150
    COGS 2021 YTD - 02 AOA Cluster Oceania Pakistan MN Medical Nutrition 2223,9234 2223,9234 5819,913 0 150
    Comm & OVE 2021 YTD - 02 AOA Cluster Oceania Pakistan MN Medical Nutrition 12,8834 12,8834 5819,913 0 150
    VDC 3P 2021 YTD - 02 AOA Cluster Oceania Pakistan MN Medical Nutrition 216,1187 216,1187 5819,913 0 150
    TDC 2021 YTD - 02 AOA Cluster Oceania Pakistan MN Medical Nutrition 5332,1178 5332,1178 5819,913 0 150
    Structural Costs 2021 YTD - 02 AOA Cluster Oceania Pakistan MN Medical Nutrition 1332,2886 1332,2886 5819,913 0 150
    NNS Aff 2021 YTD - 02 AOA Cluster Oceania Pakistan MN Medical Nutrition 2189,4962 2189,4962 0 0 150

     

    Best regards

    Community Support Team _ chenwu zhu

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      This is a mistake, den for Pakistan market is 150

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago
    kpiTIMECLUSTERSUB CLusterMarketcategoryB. Areanumnum-calcdenden-calccreated
    NNS2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition5819,9135819,913000
    OthRev2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition005819,91305819,913
    COGS2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition2223,92342223,92345819,91305819,913
    Comm & OVE2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition12,883412,88345819,91305819,913
    VDC 3P2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition216,1187216,11875819,91305819,913
    TDC2021 YTD - 02AOA ClusterOceaniaOceaniaMNMedical Nutrition5332,11785332,11785819,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 ClusterOceaniaPakistanMNMedical Nutrition6166161500150
    GPS2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition8839,12868839,12861500150
    OthRev2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition001500150
    COGS2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition2223,92342223,92341500150
    Comm & OVE2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition12,883412,88341500150
    VDC 3P2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition216,1187216,11871500150
    TDC2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition5332,11785332,11781500150
    Structural Costs2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition1332,28861332,28861500150
    NNS Aff2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition2189,49622189,496200150
    NNS2021 YTD - 02AOA ClusterOceaniaPakistanMNMedical Nutrition150150000
    • v-chenwuz-msft's avatar
      v-chenwuz-msft
      Community Support
      4 years ago

      Hi Anonymous ,

       

      Column =
VAR _o = [den]
VAR _nns_aff_index =
    MAXX (
        TOPN (
            1,
            FILTER ( 'Table', [kpi] = "NNS" ),
            ABS ( [Index] - EARLIER ( 'Table'[Index] ) ), ASC
        ),
        [Index]
    )
VAR _value =
    CALCULATE (
        MAX ( 'Table'[num-calc] ),
        FILTER ( 'Table', [Index] = _nns_aff_index )
    )
RETURN
    IF ( [kpi] = "nns aff", _value, _o )

       

      Result:

       

      Pbix in the end you can refer.

      Best Regards

      Community Support Team _ chenwu zhu

       

      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

        Hello,

         

        how could I change the formula such that it returns as the " den " the sum of the NNS with the same market. screen bellow 

        the value that should appear in Den for the NNS Aff should be the sum of NNS with the same market : 

        den should be = 2112

        Your formula works however i would like that the formula take into consideration if there is multiple NNS .

        GMHL5ClusterNum - CalcDenNombreCategoryKPIMonthYearSub-ClusterPeriod TypeMarketBusiness AreaBuy-Make-SupplyNUM aff BTypeold formulaIndexRESULT 
        InternationalLATAM438,179701MNNNS072022Andina/Platina2022 YTD - 07ArgentinaMedical Nutrition 438,1797YTD04092130
        InternationalLATAM310,178701VMHSNNS072022Andina/Platina2022 YTD - 07ArgentinaSundown 310,1787YTD04095580
        InternationalLATAM57,305701VMHSNNS072022Andina/Platina2022 YTD - 07ArgentinaOsteo Biflex 57,3057YTD04094900
        InternationalLATAM4,548101VMHSNNS072022Andina/Platina2022 YTD - 07ArgentinaSolgar 4,5481YTD04094220
        InternationalLATAM737,697801VMHSNNS072022Andina/Platina2022 YTD - 07ArgentinaNature's Bounty 737,6978YTD04093540
        InternationalLATAM563,608901CCNNS072022Andina/Platina2022 YTD - 07ArgentinaConsumer Care 563,6089YTD04092850
        InternationalLATAM15,94801MNNNS Aff072022Andina/Platina2022 YTD - 07ArgentinaMedical NutritionAFF Balance-15,948YTD438,17974092452111,519