Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
Anonymous
Not applicable

Dax formula - Select value - all except

‎08-23-2022 12:29 AM

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
Message 1 of 6
1,179 Views
0
5 REPLIES 5
Anonymous
Not applicable

‎08-29-2022 01:43 AM
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
Message 4 of 6
1,116 Views
0
v-chenwuz-msft
Community Support
Community Support
In response to Anonymous

‎08-29-2022 02:56 AM

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:

vchenwuzmsft_0-1661766985130.png

 

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.

 

Dax formula - Select value - all except.pbix
Message 5 of 6
1,107 Views
0
Anonymous
Not applicable
In response to v-chenwuz-msft

‎08-29-2022 06:20 AM

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 

akerrout_0-1661778344790.png

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

 

Message 6 of 6
1,100 Views
0
v-chenwuz-msft
Community Support
Community Support

‎08-25-2022 07:29 PM

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

Message 2 of 6
1,126 Views
0
Anonymous
Not applicable
In response to v-chenwuz-msft

‎08-29-2022 01:41 AM

This is a mistake, den for Pakistan market is 150

Message 3 of 6
1,117 Views
0

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All