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...
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.

dheeraj_ami's avatar
dheeraj_ami
Helper I
10 years ago

Anonymous  The way you presented previously i could't  understand . It would be greatful if you can share step by step process with visuals for creating level metrics.

 

Thanks
Dheeraj 

Anonymous's avatar
Anonymous
Not applicable
10 years ago

dheeraj_ami

 

1. The only step you need is to create a measure which I am calling it as BYCAT.

2. This is done by using the expression

            BYCAT=

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

 

3. As explained 

   The summarize function creates a table by grouping the SalesFactTable by ProductCategoty with  sum(Revenue) as aggregation      and the result is stored in the column "PYBC".

    To calrify further it is equivalent to writing a SQL Query

        Select ProductCategory, sum(Revenue) as "PYBC"

        from SalesFactTable

        group by ProductCategory

 

4. The outer SUMX will then use the calculated column PYBC done in the summarize function.

 

Hope this clarifies.   As for the visual you use the matrix table chart from the power Bi

 

Cheers 

CheenuSing

  • dheeraj_ami's avatar
    dheeraj_ami
    Helper I
    10 years ago

    Anonymous Sorry for bothering you frequently , please let me know what is ProductCategory and ProductCategory[ProductCategoyKey]

     

    thanks

    Dheeraj

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      dheeraj_ami

       

      1.  The data model I worked on has the following tables

            SalesFactTable

            Product 

            Product Category

            Product SubCategory

       

      2. The links between the tables are

            a) SalesFactTable --->> Product on ProductKey

            b) Product   -->>  ProductSubCategory on ProductSubCategoryKey

            c) ProductSubCategory -->> ProductCategory on ProductCategoryKey

       

      3. When you refer a column in DAX , the syntax is TableName[ColumnName]

          so ProductCategory[ProductCategoryKey] refers to the ProductCategory table and column ProductCategoryKey.

       

      If you need more clarity share your data model.

       

      Cheers

       

      CheenuSing

    • dheeraj_ami's avatar
      dheeraj_ami
      Helper I
      10 years ago

      Anonymous  I would like to discuss some techinical related questions, if you are intrested in that part and please let me know

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        dheeraj_ami

         

        Sure I would be keen , do send me a private message using the Message tab.

  • dheeraj_ami's avatar
    dheeraj_ami
    Helper I
    10 years ago

    Anonymous  As per your expression we have tried to create a measure but we couldn't meet it please follow the screen-shot below there was a error in it and needful help is required.

     

     

     

     

    • dheeraj_ami's avatar
      dheeraj_ami
      Helper I
      10 years ago

      Anonymous  this was the expression written in the image

       

      Measure = SUMX(SUMMARIZE(FactInternetSales,

      DimProductCategory[ProductCategoryKey],"ABC",CALCULATE(SUM(FactInternetSales[SalesAmount],ALL(DimProductCategory[ProductCategoryKey]))),[ABC]))

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        dheeraj_ami

         

        It is a syntax error. The correct expressioon should be

         

         

        Measure = SUMX(

                                   SUMMARIZE(FactInternetSales,
           
                                      DimProductCategory[ProductCategoryKey],"ABC",
                                      
                                      CALCULATE(
                                                   SUM(FactInternetSales[SalesAmount]),
                                                   ALL(DimProductCategory[ProductCategoryKey])
                                               )
                                      ,[ABC])

                                   )

         

        The error in your expression was SUM function. SUM() takes only one argument.  The original expression you had written was

          SUM(FactInternetSales[SalesAmount],ALL(DimProductCategory[ProductCategoryKey])) 

        which has more than one argument.  

        I had just corrected the syntax and removing extra ) at the end of the sum statement

         

        Cheers

         

        CheenuSing

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi CheenuSing,

     

    I have a Table like below 

    ParentChildNo.of Prodcuts
    AppleIphone100
    AppleIpad200
    AppleMac50
    SamsungMobile2000
    SamsungTVs300
    SamsungRefrigirators500

     

    On the PowerBi Visual, I want to show something like below, I want to calculate the 2nd measure at the parent level even when the parent is no displayed in the visual

     

    ChildNo.of ProdcutsNo.of Products (Group By at Parent level)
    Iphone100350
    Ipad200350
    Mac50350
    Mobile20002800
    TVs3002800
    Refrigirators5002800

     

     

    Thanks,

    DILIP

    • AshokkumarM's avatar
      AshokkumarM
      Frequent Visitor
      8 years ago

      I too having the same scenario, Let me know if you have find a solution