Forum Discussion

Diptarup's avatar
Diptarup
Helper II
4 years ago

Additional Measure to be defined

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
Need Help
Show and Tell

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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

    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.

  • Diptarup's avatar
    Diptarup
    Helper II
    4 years ago

    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 

     

     

    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 

     

     the table i would prefer would be 

    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.

      • Diptarup's avatar
        Diptarup
        Helper II
        4 years ago

        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])
    • Diptarup's avatar
      Diptarup
      Helper II
      4 years ago

      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.
  • Diptarup's avatar
    Diptarup
    Helper II
    4 years ago

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