Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dheeraj_ami
Helper I
Helper I

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

1 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

 

Capture.GIF

 

 

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

24 REPLIES 24
dheeraj_ami
Helper I
Helper I

@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

 

Capture.PNG

@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

 

Capture.GIF

 

 

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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

 

 

Thanks

Dheeraj

@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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

@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 

@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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Anonymous
Not applicable

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

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

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

 

 

 

Capture1.PNG 

@CheenuSing  this was the expression written in the image

 

Measure = SUMX(SUMMARIZE(FactInternetSales,

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

@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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

 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

 

 

 

 

 

 

 

Untitled1.png

@dheeraj_ami

 

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

 

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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!

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

 

thanks

Dheeraj

@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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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

@dheeraj_ami

 

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

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

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
dheeraj_ami
Helper I
Helper I

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.