Tuesday, September 20, 2022

Calculated columns and measures

 Measure DAX

Numeric columns support the greatest range of aggregation functions:

  • Sum
  • Average
  • Minimum
  • Maximum
  • Count (Distinct)
  • Count
  • Standard deviation
  • Variance
  • Median

Summarize non-numeric columns

Non-numeric columns can be summarized. However, the sigma symbol does not show next to non-numeric columns in the Fields pane because they don't summarize by default.

Text columns allow the following aggregations:

  • First (alphabetically)
  • Last (alphabetically)
  • Count (Distinct)
  • Count

Date columns allow the following aggregations:

  • Earliest
  • Latest
  • Count (Distinct)
  • Count

Boolean columns allow the following aggregations:

  • Count (Distinct)
  • Count
=================================================

Compare calculated columns with measures

Completed100 XP

DAX beginners often experience a degree of confusion about calculated columns and measures. The following section reviews the similarities and differences between both.

Regarding similarities between calculated columns and measures, both are:

  • Calculations that you can add to your data model.
  • Defined by using a DAX formula.
  • Referenced in DAX formulas by enclosing their names within square brackets.

The areas where calculated columns and measures differ include:

  • Purpose - Calculated columns extend a table with a new column, while measures define how to summarize model data.
  • Evaluation - Calculated columns are evaluated by using row context at data refresh time, while measures are evaluated by using filter context at query time. Filter context is introduced in a later module; it's an important topic to understand and master so that you can achieve complex summarizations.
  • Storage - Calculated columns (in Import storage mode tables) store a value for each row in the table, but a measure never stores values in the model.
  • Visual use - Calculated columns (like any column) can be used to filter, group, or summarize (as an implicit measure), whereas measures are designed to summarize.

Monday, September 19, 2022

Resume : Introduction to creating measures using DAX in Power BI

 

Differences between a calculated column and a measure

The fundamental difference between a calculated column and a measure is that a calculated column creates a value for each row in a table. For example, if the table has 1,000 rows, it will have 1,000 values in the calculated column. Calculated column values are stored in the Power BI .pbix file. Each calculated column will increase the space that is used in that file and potentially increase the refresh time.

Measures are calculated on demand. Power BI calculates the correct value when the user requests it. When you previously dragged the Total Sales measure onto the report, Power BI calculated the correct total and displayed the visual. Measures do not add to the overall disk space of the Power BI .pbix file.

Measures are calculated based on the filters that are used by the report user. These filters combine to create the filter context.

https://learn.microsoft.com/en-us/training/modules/create-measures-dax-power-bi/1-introduction


Use relationships effectively

To create this measure for Sales by Ship Date, you can use the DAX function USERELATIONSHIP(). This function is used to specify a relationship to be used in a specific calculation and is done without overriding any existing relationships. It is a beneficial feature in that it allows developers to make additional calculations on inactive relationships by overriding the default active relationship between two tables in a DAX expression, as shown in the following example:

Sales by Ship Date = CALCULATE(Sum(Sales[TotalPrice]), USERELATIONSHIP(Sales[ShipDate],'Calendar'[Date]))


====================================================================

Work with DAX functions

However, many functions exist that you won't find in Excel because they're specific to data modeling:

  • Relationship navigation functions
  • Filter context modification functions
  • Iterator functions
  • Time intelligence functions
  • Path functions
====================================================================


Learn about row context

However, row context doesn't extend beyond the table. If your formula needs to reference columns in other tables, you have two options:

  • If the tables are related, directly or indirectly, you can use the RELATED or RELATEDTABLE DAX function. The RELATED function retrieves the value at the one-side of the relationship, while the RELATEDTABLE retrieves values on the many-side. The RELATEDTABLE function returns a table object.
  • When the tables aren't related, you can use the LOOKUPVALUE DAX function.

Discount Amount =(
    Sales[Order Quantity]
        * RELATED('Product'[List Price]) ) - Sales[Sales Amount]
    Gunakan RELATED untuk menJOIN




          Wednesday, February 28, 2018


          McKinsey 7-S Framework: A Checklist
          Source : MBA Boost
          Here is a checklist for using the McKinsey 7-S Framework to describe a business. Note that the 7-S model was developed in the 1980’s by Robert Waterman, Tom Peters and Julien Philips while working for McKinsey and originally presented in their article “Structure is not Organization.” Also included are questions to help evaluate industry structure.

          Mckinsey 72 Model
          Strategy
          1.     In what kinds of markets does the business compete and how are these markets changing?
          o    Types of Customers
          o    Local Market
          o    Regional Market
          o    Global Market
          2.     What are the firm’s sustainable competitive advantages and how are they changing?
          o    Technical Leadership
          o    Cost
          o    Quality and Reliability
          o    Service
          o    Knowledge of the Market
          o    Control of Distribution Channels
          o    Monopoly Power through Government Policy
          o    Artificially Protected Market
          o    Government Connections
          3.     How do the basic elements of the business system work, and where are the key leverage points? How might these change?
          o    Technology
          o    Product Design
          o    Procurement
          o    Manufacturing
          o    Distribution and Selling
          o    Service
          4.     How has the firm deployed its major resources and how is this changing?
          o    Funds
          o    Management
          o    Service, Selling or other Distinctive Capabilities
          o    Market Position
          5.     What are the firm’s overriding strategic priorities?
          o    Protect Existing Market Share
          o    Penetrate New Markets
          o    Enhance Value to Customers by Lowering Production Cost, Improve Product/Service or Greater Technology Edge
          o    Acquire or Move Into New Business or Technology
          o    Change the Game
          6.     How does the firm secure needed support from its other major constituencies and how is this changing?
          o    Employees
          o    Distributors and Retailers
          o    Suppliers of Funds
          o    Suppliers of Materials and Equipment
          o    Key Governmental Bodies
          Structure
          1.     How is the company/team divided?
          2.     What is the hierarchy?
          3.     How do the various departments coordinate activities?
          4.     How do the team members organize and align themselves?
          5.     Is decision making and controlling centralized or decentralized? Is this as it should be, given what we’re doing?
          6.     Where are the lines of communication? Explicit and implicit?
          Skills
          1.     What business activities that are crucial to its successes is the firm good at performing and how are these changing?
          2.     What business activities that are crucial to its success is the firm weak in performing and how are these changing?
          3.     What important management activities must the company perform much better than it does now?
          o    Business System Functions
          o    Nonbusiness System Functions
          §  Employee Relations
          §  Staff Development
          §  Government Relations
          o    Special Management Challenges
          §  Multi-Product Line Management
          §  Acquisition Management
          §  Resource Deployment
          §  Divestment
          §  “Privatization”
          4.     Is this an “excellent” firm?
          o    Action and Execution Focus
          o    Close to the Customer
          o    Productivity Through People Focus
          o    Simple Form, Lean Staff
          o    Stick to their Knitting
          o    Hands-on, Value-Driven
          o    Simultaneous Loose-Tight Properties
          Shared Values
          1.     How do people in the firm describe the ways in which it is distinctive?
          2.     How do people in the firm describe what is key about “how we do things around here?”
          o    Controlling Considerations in Decision Making
          §  Impact of Priority Customers
          §  Internal Political Considerations
          §  Financial Attractiveness
          §  Impact on Market Position
          §  Donor or Government Requirements or Expectations
          o    How Important Constituencies are Dealt with
          o    How Things get done
          o    How decisions are made
          o    What preoccupies senior management
          3.     What things get the most and the least top-management attention and how is this changing?
          o    Business Functions
          o    Markets and product Lines
          o    Organizational Units
          o    Projects
          o    Threats
          o    Cost Versus Value to Customer
          o    Shore Versus Long Run
          o    Internal Versus External
          4.     Does management give much importance to the firm’s culture and shared values?
          5.     Does the firm have a true guiding concept that is
          o    Directional
          o    Fundamental
          o    Plausible
          o    Achievable
          o    Shared
          And does the guiding concept fit with the strategy and skills of the firm?
          Systems
          1.     What are the most important management processes that top management uses to run this firm?
          o    Annual Strategy and/or Performance Review
          o    Monthly or Quarterly Operating Reviews
          o    Capital Investment Approvals
          o    “Kitchen-Cabinet Sessions”
          o    “Management by Walking Around”
          2.     What are the most striking characteristics of these processes?
          o    Formal versus Informal
          o    Financial versus Substantive
          o    Staff versus Line-Dominated
          o    Multilateral versus Bilateral
          3.     What managerial functions are most and least emphasized in the conduct of these processes and what are the trends?
          o    Path Setting
          o    Problem Finding
          o    Decision Making
          o    Execution
          o    Interpretation
          4.     What are the most important information systems in the firm? What variables are monitored and controlled most closely?
          Style
          1.     What is top management’s apparent view of its own principal value-add?
          o    Monitor, review and ensure results
          o    Make sure others answer tough questions before they make decisions
          o    Make tough decisions directly
          o    Stay on top of internal operations
          o    Identify issues for attention
          o    Relate with outside world
          o    Maintain internal conditions for effective function
          o    Chart course and set direction
          2.     For people in the firm, what are the most-watched indicators of top-management expectations and values?
          o    Recent decisions
          o    More or less formal pronouncements
          o    Informal questioning routines
          o    Praise and promotion or criticism
          3.     How does top management make decisions?
          o    Wide consultation versus narrow consultation versus solitary decision making
          o    Reliance on data and analysis versus trusted counsel versus lessons of experience
          4.     How does top management seek to motivate employees?
          o    Orders
          o    Compensations and perquisites
          o    Personal relations
          o    Hoopla
          o    Participation and team spirit
          o    Internal competition
          Staff
          1.     What are the demographics of the management group and how are they changing?
          o    Functional and business unit experience within the company
          o    Educational background/professional discipline
          o    Experience outside the company
          o    Religion, political party, age, sex, regional factions
          2.     What kinds of things are current senior managers known for and what is the “next generation” of managers known for?
          o    Important accomplishments
          o    Interests and personal crusades
          o    Connections
          3.     Where in the firm are the strongest managers found? The weakest?
          4.     How much importance does the firm really give to its people?
          o    Emphasis on development planning, performance appraisal, training, etc.
          5.     What is the top-management structure and how is it changing?
          o    One man versus CEO/COO versus team
          o    Collective executive versus collection of top managers of various organizational units
          o    Government representation or oversight
          o    Board as review versus deliberative versus directive body 
          6.     Are “special” structural devices used?
          o    Task Forces
          §  Full time versus part time
          §  Volunteers versus draftees
          §  Reports versus results
          o    Czars
          o    Project managers
          o    Quality circles
          o    Management “panels” or “boards”
          7.     How does this firm deal with functions that could be internal or external and how is this changing?
          o    Distribution and sales
          o    Materials supply, subassembly, etc.
          o    Technology supply
          Evaluating Industry Structure
          Threat of New Entrants
          1.     Do large firms have a cost or performance advantage in your segment of the industry?
          2.     Are there any proprietary product differences in your industry?
          3.     Are there any established brand identities in your industry?
          4.     Do your customers incur any significant costs in switching suppliers?
          5.     Is a lot of capital needed to enter your industry?
          6.     Is serviceable used equipment expensive?
          7.     Does the newcomer to your industry face difficulty in accessing distribution channels?
          8.     Does experience help you to continuously lower costs?
          9.     Does the newcomer have any problems in obtaining the necessary skilled people, materials or suppliers?
          10. Does your product or service have any proprietary features which give you lower costs?
          11. Are there any licenses, insurance or qualifications which are difficult to obtain?
          12. Can the newcomer expect strong retaliation on entering the market?
          High barriers to entry = + factors (favorable to industry)
          Low barriers to entry = – factors (unfavorable to industry)
          Threat of Substitutes
          (Some other product or service which performs the same jobs as yours)
          1.     Substitutes have performance limitation which do not completely offset their lowest price or their performance advantage is not justified by their higher price.
          2.     The customer will incur costs in switching to a substitute.
          3.     Your customer has no real substitute.
          4.     Your customer is not likely to substitute
          Low threat of substitution = + factors (favorable to industry)
          High threat of substitution factors (unfavorable to industry)
          Bargaining Power of Suppliers
          1.     My inputs (materials, labor, supplies, services, etc.) are standard rather than unique or differentiated.
          2.     I can switch between suppliers quickly and cheaply.
          3.     My suppliers would find it difficult to enter my business or my customers would find it difficult to perform my function in house.
          4.     I can substitute inputs readily.
          5.     I have many potential suppliers.
          6.     My business is important to my suppliers.
          7.     My cost of purchases has a significant influence on my overall costs.
          Low bargaining power of suppliers = + factors (favorable to industry)
          High bargaining power of suppliers = – factors (unfavorable to industry)
          Determinants of Rivalry Among Existing Competitors
          1.     The industry is growing rapidly.
          2.     The industry is not cyclical with intermittent over-capacity.
          3.     The fixed costs of the business are a relatively low portion of total costs.
          4.     There are significant product differences and brand identities between the competitors.
          5.     The competitors are diversified rather than specialized.
          6.     It would not be hard to get out of this business because there are no specialized skills and facilities or long term contract commitments, etc.
          7.     My customers would include significant costs in switching to a competitor.
          8.     My product is complex and requires a detailed understand on the part of my customer.
          9.     All my competitors are approximately my size.
          Low level of rivalry = + factors (favorable to industry)
          Higher level of rivalry factors (unfavorable to industry)
          Overall Industry Rating
          Threat of new entrants = __
          Threat of substitutes = __
          Bargaining power of suppliers = __
          Bargaining power of buyers = __
          Rivalry among the existing companies = _
          Related Posts Plugin for WordPress, Blogger...