• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Measure for Status of 'Complete' over multiple workbooks|Power Pivot|Excel Forum|My Online Training Hub

You are here: Home / Measure for Status of 'Complete' over multiple workbooks|Power Pivot|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower PivotMeasure for Status of 'Complete' ov…
sp_PrintTopic sp_TopicIcon
Measure for Status of 'Complete' over multiple workbooks
Avatar
Francis Kinsler
Member
Members
Level 0
Forum Posts: 6
Member Since:
December 7, 2020
sp_UserOfflineSmall Offline
1
December 7, 2020 - 11:07 pm
sp_Permalink sp_Print sp_EditHistory

Hi. I'm trying to create a measure that will show progress of tasks completed against Milestones.

My data is in a star schema in Power Pivot centred around Milestone as the only Fact table with all other tables Dimension tables - all with different columns an potentially different column headings.

I don't know whether I should be using a DAX formula in PP or merging the data and cleansing in Power Query. I'm new to DAX.

I currently have a dashboard that shows a bar chart of the number of items from each Dimension table against each project Milestone. This works fine, but I want to show a headline metric how many of those items have the status of Complete against each Milestone. I intend to link the measure into a slicer based on Milestone, hence, when Milestone 1 is selected it will show how complete that milestone is.

I'm comfortable setting up the slicer, but need help creating and calculating and best displaying the metric. It should be a simple % of status complete of the total tasks in the selected Milestone.

Thanks in advance.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
December 8, 2020 - 2:17 pm
sp_Permalink sp_Print

Hi Francis,

A simple measure should be able to count the completed ones:

CALCULATE(COUNT(SourceTable[Measures]),SourceTable[Status]="Complete")

Another count with all measures should give you the reference for comparison.

Without a sample file, hard to see how data looks, you will have to attach one.

Avatar
Francis Kinsler
Member
Members
Level 0
Forum Posts: 6
Member Since:
December 7, 2020
sp_UserOfflineSmall Offline
3
December 8, 2020 - 5:53 pm
sp_Permalink sp_Print

Thanks Catalin.

Each Dimension table is connected to the Milestone Fact table using a Milestone reference. Each Dimension table has a Status column with different values, although most have Complete or Completed as the status I want to count.

All tables are brought into the data model using Power Query. I believe I will need to cleanse the status fields such that they are all called the same (eg Status) and contain the same status values (eg change Completed to Complete where appropriate).

I believe I need two counts, one for the Total number of items per Milestone (across all Dimension tables) and one for the Total number of items with Status = Complete to create a set of pecentages. Probably best to use a PowerPivot table to create this or should I use Dax? This is where I'm getting unstuck. I'm ploughing through the Definitive Guide to Dax book (p70) but it's heavy going.

Then, create a Gauge or similar that is linked into the table and changes when the Milestone is selected.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
December 8, 2020 - 8:33 pm
sp_Permalink sp_Print

Probably best to use a PowerPivot table to create this or should I use Dax?

DAX is the language used in PowerPivot, they are not alternatives.

2 separate counts is what I mentioned, you have in the previous message a sample DAX formula for the complete status count, the full count should be the same without the filter:
CALCULATE(COUNT(SourceTable[Measures]))

You have to understand how Power Pivot works before building solutions, it's hard to fly with small wings.

Avatar
Francis Kinsler
Member
Members
Level 0
Forum Posts: 6
Member Since:
December 7, 2020
sp_UserOfflineSmall Offline
5
December 9, 2020 - 2:22 am
sp_Permalink sp_Print sp_EditHistory

Thanks. I get PP ok but was thinking that as I need to be able to use a slicer on the Milestone I'm going to need a table of counts of the Complete status (ie one for each Milestone) unless that's what your table of Measures is in your example. I'll have a play around with your examples but I've never done this before so patience is a virtue.

Struggling with the first part of your formula... SourceTable(Measures)?

If the table that I'm counting the status from is called BusinessChange how would the DAX look?

eg CALCULATE(COUNT(BusinessChange[????],BusinessChange[Status]="Complete")

What goes in the ???? and where does the result go?

Also, do I have to create a separate measure for each Dimension table status "Complete" count?

I told you I'm a novice. Just need to get my head around it.

Thanks.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
December 9, 2020 - 4:03 am
sp_Permalink sp_Print

CALCULATE(COUNT(BusinessChange[AnyColumnWithoutBlanks],BusinessChange[Status]="Complete")

Like in excel sheet formulas, a COUNT can be applied to any column, as long as there are no blanks, the result will be the same.

where does the result go?

[I get PP ok...]

You have to understand how Power Pivot works before building solutions.

PP is not just a pivot table, adding a power pivot table to sheet is not a challenge. Organizing the data model, building relationships and writing measures is the challenge, you have to take into account the entire context: data model relationships, fields used in the pivot, filters context. A measure can work as expected with some fields, but might not work in another pivot context, if you add other fields.

The result goes nowhere, you have to add the measure in the pivot table, the results will be calculated depending on the power pivot context.

Avatar
Francis Kinsler
Member
Members
Level 0
Forum Posts: 6
Member Since:
December 7, 2020
sp_UserOfflineSmall Offline
7
December 9, 2020 - 7:58 pm
sp_Permalink sp_Print sp_EditHistory

Ok thanks.

As I said in my first post, I have my data organised in PP in a Star schema data model centred around Milestone as the Fact table with a number of other Dimension tables sitting around it. The reference tables are all joined to the Milestone table with 1-many relationships with the 1 end going into the Milestone table. They are all joined using a Milestone reference field in the Dimension tables. I'm happy with that. I have also built a dashboard that I am happy with using PP.

What I am trying to achieve is a headline metric that shows overall progress of tasks (ie line items from each Dimension table) where the status in those line items is 'Complete'.

I'm sure to a person who does this all the time it can be done in 5 minutes, but for someone who has never used DAX before it is understanding 'where I put things' is probably the best way I can describe it. Hence, when I create a measure in PowerPivot, do I need to create separate measures for each Dimension table? If so, I guessing that I need to have the table selected from the data model, either in diagram view or have the table on view in data view. Then it must put the measure somewhere so that it can be used. Then somehow I need to pull these separate measures together somehow so that I can create a metric in my dashboard (say a gauge) such that I can add it to my Milestone slicer in the dashboard. OR do the measures all get calculated together in a single statement and put into a PP generated pivot table ie maybe I do not need to use DAX at all - I don't know.

To an expert this is bread and butter, to someone new to it - that's why I'm here asking dumb questions. Once I've been through the pain barrier, I'll be ok but it's not easy getting there.

CALCULATE(COUNT(BusinessChange[AnyColumnWithoutBlanks],BusinessChange[Status]="Complete") - now with this additional piece of information, I have a working measure (but had to change COUNT to COUNTA to get it to work). Also, I now know that I need to repeat this measure for each Dimension table and I need to apply a filter to the DAX to only select the current month - ie the latest data. Oh yes and it has put the result somewhere - as an fx field at the end of the available fields in the Milestone table. It remains a mystery as to why it has put it there, but I'll assume for now that I don't need to worry where it lives.

Thanks for your help. I'll let you know how I get on combining the measures into a single headline metric.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
December 9, 2020 - 9:16 pm
sp_Permalink sp_Print

Dear friend,

as mentioned in my first post, without seeing a sample file is hard to visualize your structure.

It can be done in 5 minutes, but not without a sample file that will answer other questions like why "the 1 end going into the Milestone table"?.
The dimension table is supposed to have unique keys, the fact table can have multiple keys.

We cannot recreate your data based on your description. It is your task to provide a sample file that will save time and will provide more details than you can write in 2000 words.

We will gladly help you, but we cannot spend hours recreating your data structure.

Thank you for understanding

Avatar
Francis Kinsler
Member
Members
Level 0
Forum Posts: 6
Member Since:
December 7, 2020
sp_UserOfflineSmall Offline
9
December 10, 2020 - 5:22 am
sp_Permalink sp_Print sp_EditHistory

My solution...

=CALCULATE(

                     COUNTA(

                                   BusinessChange[Id]

                                  ),

                                   BusinessChange[Status] = "Complete",

                    FILTER(

                                   BusinessChange[IsCurrentMonth] = "Yes"

                                )

                    )

where [IsCurrentMonth] is a DAX column that calculates whether the Date is in the current month or not to filter out the latest transactions...

= IF(

        YEAR ( BusinessChange[Data] = YEAR ( TODAY () )

   && MONTH ( BusinessChange[Date] = MONTH ( TODAY () ),

         "Yes",

         "No"

      )

Now to calculate similar for the other dozen or so Fact tables.

Yes, you are right it is my Dimension table that is in the middle of the Star with the 1 attached. The dozen or so Fact tables are the many end of the relationships. I'm used to calling them Reference tables (Dimensions) and Transactional tables (Facts) from a data modelling background.

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
10
December 10, 2020 - 1:57 pm
sp_Permalink sp_Print

Yes, you are right it is my Dimension table that is in the middle of the Star with the 1 attached. The dozen or so Fact tables are the many end of the relationships.

Glad to hear you managed to make it work.

Now you see how confusing a description without a file can be 🙂

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Andy Kirby, Chandler Davis
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Len Matthews
Kristine Arthy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27210

 

Member Stats:
Guest Posters: 49
Members: 31878
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.