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 ( takin...
Data Protection
Need Help
Show and Tell
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