Reply
Regular Visitor
Posts: 19
Registered: ‎07-22-2016
Accepted Solution

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


Accepted Solutions
Super User
Posts: 624
Registered: ‎02-17-2016

Re: How to create level measure

@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


All Replies
Regular Visitor
Posts: 19
Registered: ‎07-22-2016

Level measures

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

Regular Visitor
Posts: 19
Registered: ‎07-22-2016

Re: How to create level measure

@Greg_Decklerplease help me out how to create a level measure
Super User
Posts: 624
Registered: ‎02-17-2016

Re: Level measures

@dheeraj_ami

 

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

 

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

Proud to be a Datanaut!
Super User
Posts: 624
Registered: ‎02-17-2016

Re: How to create level measure

Hi @dheeraj_ami

 

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

 

Cheers

 

CheenuSing

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

Proud to be a Datanaut!
Regular Visitor
Posts: 19
Registered: ‎07-22-2016

Re: How to create level measure

Regular Visitor
Posts: 19
Registered: ‎07-22-2016

Re: How to create level measure

@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

Super User
Posts: 624
Registered: ‎02-17-2016

Re: How to create level measure

@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!
Highlighted
Regular Visitor
Posts: 19
Registered: ‎07-22-2016

Re: How to create level measure

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

 

 

Thanks

Dheeraj

Super User
Posts: 624
Registered: ‎02-17-2016

Re: How to create level measure

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