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
Diptarup
Helper II
Helper II

Additional Measure to be defined

‎10-01-2021 07:12 AM

I have this below table for which, I need to determine the average value per Product category based on the country. I am able to get the values using the below measure . But I am getting stuck when I have to adjust the value on the Parameter P1 & P2.

 

Measure to get the initial value :-  

Global Average = Var tble= SUMMARIZE(FILTER('table','table'[Year]='table'[MX-Year]),'table'[country],"AVG",SUMX(VALUES('table'[Product Name]),AVERAGE('table'[Value])))
Return AVERAGEX(tble,[AVG])
 

P1 is 0.59 of the total value of P1 in Product Category B and P2 is 0.41 of the total value of P2 in Product Category B.

So Product Category B would be P1*0.59+P2*0.41.

 

One thing to remember, not alaways Parameter P1 & P2 would be present for Product Category B. If it is not there, then it should perform the above given measure.   

 

Once you have derived the total value per Product category , then you have again re-adjust the value for the Parameter P1 & P2 for Product Category B only and all other value remains the same.

 

Any help will be highly appreciated.

 

Table
YearCountryProduct CategoryProduct NameSub - CategoryParamterValue
2021IndiaAIronC1No0
2021IndiaAIronC2No1
2021IndiaAIronC3No1
2021IndiaAIronC4No0
2021IndiaAIronC5No1
2021IndiaAIronC6No0
2021IndiaAIronC7No0
2021IndiaAIronC8No0
2021IndiaAIronC9No0
2021IndiaAIronC10No0
2021IndiaAIronC11No1
2021IndiaAIronC12No1
2021IndiaAAluminiumC1No0
2021IndiaAAluminiumC2No1
2021IndiaAAluminiumC3No1
2021IndiaAAluminiumC4No0
2021IndiaAAluminiumC5No0
2021IndiaAAluminiumC6No0
2021IndiaAAluminiumC7No0
2021IndiaAAluminiumC8No0
2021IndiaAAluminiumC9No1
2021IndiaAAluminiumC10No1
2021IndiaAAluminiumC11No1
2021IndiaAAluminiumC12No0
2021IndiaASteelC1No1
2021IndiaASteelC2No0
2021IndiaASteelC3No1
2021IndiaASteelC4No0
2021IndiaASteelC5No1
2021IndiaASteelC6No0
2021IndiaASteelC7No0
2021IndiaASteelC8No1
2021IndiaASteelC9No1
2021IndiaASteelC10No0
2021IndiaASteelC11No1
2021IndiaASteelC12No1
2021IndiaACopperC1No0
2021IndiaACopperC2No0
2021IndiaACopperC3No0
2021IndiaACopperC4No0
2021IndiaACopperC5No0
2021IndiaACopperC6No0
2021IndiaACopperC7No1
2021IndiaACopperC8No0
2021IndiaACopperC9No0
2021IndiaACopperC10No0
2021IndiaACopperC11No0
2021IndiaACopperC12No1
2021IndiaBWoodC1P11
2021IndiaBWoodC2P10
2021IndiaBWoodC3P11
2021IndiaBWoodC4P11
2021IndiaBWoodC5P11
2021IndiaBWoodC6P10
2021IndiaBWoodC7P10
2021IndiaBWoodC8P11
2021IndiaBWoodC9P10
2021IndiaBWoodC10P10
2021IndiaBWoodC11P10
2021IndiaBWoodC12P10
2021IndiaBCottonC1P10
2021IndiaBCottonC2P11
2021IndiaBCottonC3P11
2021IndiaBCottonC4P11
2021IndiaBCottonC5P10
2021IndiaBCottonC6P10
2021IndiaBCottonC7P10
2021IndiaBCottonC8P11
2021IndiaBCottonC9P10
2021IndiaBCottonC10P10
2021IndiaBCottonC11P10
2021IndiaBCottonC12P10
2021IndiaBGlassC1P20
2021IndiaBGlassC2P21
2021IndiaBGlassC3P21
2021IndiaBGlassC4P21
2021IndiaBGlassC5P20
2021IndiaBGlassC6P20
2021IndiaBGlassC7P20
2021IndiaBGlassC8P21
2021IndiaBGlassC9P20
2021IndiaBGlassC10P20
2021IndiaBGlassC11P20
2021IndiaBGlassC12P20
2021IndiaBPlasticC1P21
2021IndiaBPlasticC2P20
2021IndiaBPlasticC3P20
2021IndiaBPlasticC4P21
2021IndiaBPlasticC5P20
2021IndiaBPlasticC6P21
2021IndiaBPlasticC7P20
2021IndiaBPlasticC8P21
2021IndiaBPlasticC9P20
2021IndiaBPlasticC10P20
2021IndiaBPlasticC11P20
2021IndiaBPlasticC12P20
2021IndiaCBikeC1No1
2021IndiaCBikeC2No1
2021IndiaCBikeC3No1
2021IndiaCBikeC4No0
2021IndiaCBikeC5No0
2021IndiaCBikeC6No0
2021IndiaCBikeC7No0
2021IndiaCBikeC8No0
2021IndiaCBikeC9No0
2021IndiaCBikeC10No0
2021IndiaCBikeC11No1
2021IndiaCBikeC12No1
2021IndiaCCarC1No1
2021IndiaCCarC2No1
2021IndiaCCarC3No1
2021IndiaCCarC4No0
2021IndiaCCarC5No0
2021IndiaCCarC6No0
2021IndiaCCarC7No0
2021IndiaCCarC8No0
2021IndiaCCarC9No0
2021IndiaCCarC10No0
2021IndiaCCarC11No1
2021IndiaCCarC12No1
2021IndiaCCycleC1No1
2021IndiaCCycleC2No1
2021IndiaCCycleC3No1
2021IndiaCCycleC4No0
2021IndiaCCycleC5No1
2021IndiaCCycleC6No1
2021IndiaCCycleC7No1
2021IndiaCCycleC8No1
2021IndiaCCycleC9No1
2021IndiaCCycleC10No1
2021IndiaCCycleC11No0
2021IndiaCCycleC12No1
2021IndiaCCartC1No1
2021IndiaCCartC2No1
2021IndiaCCartC3No1
2021IndiaCCartC4No0
2021IndiaCCartC5No1
2021IndiaCCartC6No1
2021IndiaCCartC7No1
2021IndiaCCartC8No1
2021IndiaCCartC9No1
2021IndiaCCartC10No1
2021IndiaCCartC11No0
2021IndiaCCartC12No1
2021IndiaD E1No4
2021IndiaD E2No3
2021IndiaD E3No2
Labels:
Message 1 of 8
1,095 Views
0
7 REPLIES 7
Diptarup
Helper II
Helper II

‎10-07-2021 01:36 AM

Can somebody help on the below query raised. Would really appreciate any help on the query posted.

Message 8 of 8
852 Views
0
Diptarup
Helper II
Helper II

‎10-04-2021 08:51 AM

Hi Eyelyn,

 

Many Thanks for taking time out for the query. You need to use the formula that I have used above to determine the value as this will be rolling average. This is output that will come 

 

Diptarup_1-1633361753758.png

 

what I am expecting is as per the next condition , since I have P1 and P2 in fornt of B respectively, the rolling average should reflect 

 

Diptarup_2-1633362020610.png

 the table i would prefer would be 

Diptarup_3-1633362269885.png

I am unable to attach the PBix . One thing to note since for country US the Parameters are not marked as P1 & P2 the Rolling Avarge and Revised Value would be same.

Message 3 of 8
956 Views
0
Diptarup
Helper II
Helper II
In response to Diptarup

‎10-04-2021 09:06 AM

Additional Measures used:-

 

MAX-Year = MAX('Table'[Year])
Criteria = VAR categoryNumber = If('Table'[Sub - Category]="E1"||'Table'[Sub - Category]="E2"||'Table'[Sub - Category]="E3","-",VALUE(RIGHT('Table'[Sub - Category],LEN('Table'[Sub - Category])-1)))
RETURN
SWITCH(TRUE(),
categoryNumber<=4, "C1",
categoryNumber<=8, "C2",
categoryNumber<=12,"C3",
BLANK()
)
Rolling Global Average = Var tble= SUMMARIZE(FILTER('Table','Table'[Year]='Table'[MAX-Year]),'Table'[Country],"AVG",SUMX(VALUES('Table'[Product Name]),AVERAGE('Table'[Value])))
Return AVERAGEX(tble,[AVG])
 
Since US data is to heavy to load you can use India and change it to US and in parameter section make it No.
Message 6 of 8
923 Views
0
smpa01
Super User
Super User
In response to Diptarup

‎10-04-2021 09:13 AM

@Diptarup  can you provide the pbix?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Message 7 of 8
885 Views
0
smpa01
Super User
Super User
In response to Diptarup

‎10-04-2021 09:05 AM

@Diptarup  the measure you provided, does not work

 

smpa01_0-1633363511025.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Message 4 of 8
933 Views
0
Diptarup
Helper II
Helper II
In response to smpa01

‎10-04-2021 09:08 AM

Kindly check my above post all the measures are now given there. Pasting below for you benefit.

 

MAX-Year = MAX('Table'[Year])
Criteria = VAR categoryNumber = If('Table'[Sub - Category]="E1"||'Table'[Sub - Category]="E2"||'Table'[Sub - Category]="E3","-",VALUE(RIGHT('Table'[Sub - Category],LEN('Table'[Sub - Category])-1)))
RETURN
SWITCH(TRUE(),
categoryNumber<=4, "C1",
categoryNumber<=8, "C2",
categoryNumber<=12,"C3",
BLANK()
)
Rolling Global Average = Var tble= SUMMARIZE(FILTER('Table','Table'[Year]='Table'[MAX-Year]),'Table'[Country],"AVG",SUMX(VALUES('Table'[Product Name]),AVERAGE('Table'[Value])))
Return AVERAGEX(tble,[AVG])
Message 5 of 8
906 Views
0
Anonymous
Not applicable

‎10-03-2021 08:38 PM

Hi @Diptarup ,

 

1. I used the following formula to calculate the average of each Category and Parameter

=CALCULATE(AVERAGE('Table'[Value]),ALLEXCEPT('Table','Table'[Product Category],'Table'[Paramter]))

As the output shown, P1 in Product Category B =0.38 not 0.59 ; P2 in Product Category B =0.33 not 0.41

Eyelyn9_1-1633318541740.png

2. Since you want to calculate P1*0.59+P2*0.41 , it seems that Parameter column should be Int type not Text type,right?

 

Please provide me with more details about your table and your expected output or share me with your pbix file after removing sensitive data.

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Message 2 of 8
980 Views
0

Helpful resources

Announcements
Power BI DataViz World Championships

Power BI Dataviz World Championships

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

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

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