Forum Discussion

dheeraj_ami's avatar
dheeraj_ami
Helper I
10 years ago
Solved

How to create level measure

HI

 

How to create a measure with a level (Level Metrics in microstrategy)

for eg: i need a report like SubCategory,SumOfRevenue,SumOfRevenue For Category where Category attribute should not be in report

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    10 years ago

    dheeraj_ami

     

    I tried using a sample data set in excel power pivot and it worked.

     

    Create a meaure called BYCAT using the expression

    BYCAT=

    SUMX(
    SUMMARIZE( SalesFactTable
    ,ProductCategory[ProductCategoryKey]
    ,"PYBC", Calculate(sum([Revenue],ALL(ProductCategory[ProductCategoryKey]))
    )
    ,[PYBC]
    )

     

    I am assuming you have the necessary relationship built between the various tables.

     

    Create a cross table using this and you should be able to see the result the way you want.

    The sample output in the excel power pivot model tried by me is

     

     

     

    Try it out and if it works please accept this as a solution and also give kudos.

24 Replies

  • dheeraj_ami's avatar
    dheeraj_ami
    Helper I
    10 years ago

    how can i create a measure with a level (Level Metrics in microstrategy)

    for eg: i need a report like SubCategory,SumOfRevenue,SumOfRevenue For Category where Category attribute should not be in report

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      dheeraj_ami

       

      Can you share some samples of the data and the visual you want to achieve

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      Hi dheeraj_ami

       

      Can you please share some data and the visuals you are expecting.

       

      Cheers

       

      CheenuSing

  • dheeraj_ami's avatar
    dheeraj_ami
    Helper I
    10 years ago

    Anonymous   The below picture shows the level measure (category_revenue) this measure is based on category column and beside category_revenue column there is revenue column this is based on sub-category column so, when you add (SUM) up all sub-category's in single category then category_revenue column is defined.

    Could you please help me in this doing in power BI

     

    Thanks,

    Dheeraj

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      dheeraj_ami

       

      I tried using a sample data set in excel power pivot and it worked.

       

      Create a meaure called BYCAT using the expression

      BYCAT=

      SUMX(
      SUMMARIZE( SalesFactTable
      ,ProductCategory[ProductCategoryKey]
      ,"PYBC", Calculate(sum([Revenue],ALL(ProductCategory[ProductCategoryKey]))
      )
      ,[PYBC]
      )

       

      I am assuming you have the necessary relationship built between the various tables.

       

      Create a cross table using this and you should be able to see the result the way you want.

      The sample output in the excel power pivot model tried by me is

       

       

       

      Try it out and if it works please accept this as a solution and also give kudos.

      • dheeraj_ami's avatar
        dheeraj_ami
        Helper I
        10 years ago

        Anonymous  Hi wil u please explain the expression and please let me know what in mean by "PYBC" in the expression

         

         

        Thanks

        Dheeraj