How to create level measure

[dheeraj_ami]

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

[dheeraj_ami]

@CheenuSing   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

[CheenuSing · Accepted solution]

@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]

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

Thanks

Dheeraj

[CheenuSing]

@dheeraj_ami

The expression 

BYCAT=

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

The summarize function creates a table by grouping the SalesFactTable with the Column Name "PYBC" as sum(Revenue).

Then the SUMX evaluates the created column [PYBC] for each row in the filter context and returns the value in BYCAT.

Hope this clarifies your query.

Cheers

CheenuSing

[dheeraj_ami]

@CheenuSing  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 

[CheenuSing]

@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

[Anonymous]

Hi CheenuSing,

I have a Table like below 

Parent	Child	No.of Prodcuts
Apple	Iphone	100
Apple	Ipad	200
Apple	Mac	50
Samsung	Mobile	2000
Samsung	TVs	300
Samsung	Refrigirators	500

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

Child	No.of Prodcuts	No.of Products (Group By at Parent level)
Iphone	100	350
Ipad	200	350
Mac	50	350
Mobile	2000	2800
TVs	300	2800
Refrigirators	500	2800

Thanks,

DILIP

[AshokkumarM]

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

[dheeraj_ami]

@CheenuSing  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]

@CheenuSing  this was the expression written in the image

Measure = SUMX(SUMMARIZE(FactInternetSales,

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

[CheenuSing]

@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

[dheeraj_ami]

 Hi   @CheenuSing I tried with the Expression, the expression excuted with out any errors and we did not met with the requirement.We are not getting values based on State Level, getting values based on city level only and the grand total value is not same, Kindly solve the issue

[CheenuSing]

@dheeraj_ami

Can you please share the PBIX file for me to check.

[smp0150]

Hi - Was there ever any resolve for the issue? I'm trying to do a level meausre - but only have data for one table. Thanks!

[dheeraj_ami]

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

thanks

Dheeraj

[CheenuSing]

@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]

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

[CheenuSing]

@dheeraj_ami

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

[CheenuSing]

@dheeraj_ami

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

[dheeraj_ami]

@CheenuSing