cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Community Champion

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

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

Proud to be a Datanaut!
24 REPLIES 24
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.

Thanks,

Dheeraj

Community Champion

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

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

Proud to be a Datanaut!
Helper I

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

Thanks

Dheeraj

Community Champion

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

Cheers

CheenuSing

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

Proud to be a Datanaut!
Helper I

@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

Community Champion

@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

 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

Frequent Visitor

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

Helper I

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

Helper I

@CheenuSing  this was the expression written in the image

Measure = SUMX(SUMMARIZE(FactInternetSales,

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

Community Champion

@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!
Helper I

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

Community Champion

@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!
Frequent Visitor

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!

Helper I

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

thanks

Dheeraj

Community Champion

@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!
Helper I

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

Community Champion

@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!
Community Champion

@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!
Helper I

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors