Measures to remove columns…

[Dbrunts0888_]

Hello All

im new to this and I've already hit a stumbling block after trying to move training courses into real life...

Margin lookup table shows w.e date and margin amount (it changes at different times in year)

misc charge lookup shows rate by site so I can convert misc charge into hours

hours spend data table shows total hours worked and misc columns...

misc columns converted into hours via above (calculated lookup values column)

I've had to create another calculated column to pull in margin using if statements rather than a measure. 
can this be done? 

[Dbrunts0888_]

@tamerj1 has this worked?

https://we.tl/t-1z4UJaidgH

[tamerj1]

@Dbrunts0888_ 
Most probably yes you can. Can you please share a screenshot of your data model showing column names in each table?

[Dbrunts0888_]

Hi @tamerj1 

I just added this in as a reply 

[VahidDM]

Hi @Dbrunts0888_ 

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[Dbrunts0888_]

Hi VahidDM,

Apologies for the lack of information... hopefully the below will help a little more.

Below are the tables / relationships I have built so far:

HoursSpend_Data:

This table holds all the hours by ID number + any MISC Payments for the colleagues.

I have had to bring in Margin PH & MISC Charge rate as calculated columns rather than being able to do calculations as measures.

Margin PH Lookup:

Shows each weekending data & the margin associated to that week (it changes at certain times in the year)

Travel Hours Lookup:

Shows the Site ID, W.E Date and charge rate (again these charge rates change throughout the year)

What I am trying to achieve is trim down the table width of the hours spend by not having to store the static values as calculated columns. I need too:

Take the MISC Charge 1 & 2 columns from Hours Spend Data & Divide it by the correct charge rate by Site ID & W.E Date - in turn this will spit out a 2 decimal place figure (MISC Hours)

I then need to combine Total Worked Hours & Total MISC Hours together (Total Combined Hours)

I then need to take the Total COmbined Hours & multiple it by the Margin PH depending on the W.E Date of the hours.

To explain, we have a team of Administrators doing this manually every single week, so I thought this was a good test to automate and use the skills from the courses I have been on... I have managed to automate, but would prefer to remove calculated columns and look at developing more advanced DAX to make the performance and size better.

Administrators currently take Temp ID from lookup list, open up relevant payroll file, if worked calculated the hours and type into a manual tracker - then apply the margin. It takes them around 2-3 hours a week where as this now only takes 5 minutes.

Really appreciate you taking the time to reply and hopefully this gives you a little more context.

Thanks

[tamerj1]

Hi @Dbrunts0888_ 
Would you like to show the results on a pivot table? Sliced by date heirarchy for eaxample? Or you need only a calculated column in one of the tables? Please provide details on the desired result. Thank you!

[Dbrunts0888_]

Hi @tamerj1 

The output is to have 2 pivot tables:

Summary Tab:

Showing Branch & Total Margin by W.E Date

Colleague Summary Tab:

Showing Branch, Colleague ID / Name (Removed from Sample Data), Total Hours & Margin Payback by W.E Date... example below:

I just wanted to learn how to make things more efficient and move away from the lookup minsdset of old. Measure over CC.

[tamerj1]

@Dbrunts0888_ 
Ok now is much more clear 🙂 Is is possible that you can share a sample data file? 

[Dbrunts0888_]

How can you attach sample data here? (Sorry really new to this)

Really appreciate your time.

[tamerj1]

@Dbrunts0888_ 
Upload to WeTransfer for example and send the link

[Dbrunts0888_]

@tamerj1 has this worked?

https://we.tl/t-1z4UJaidgH

[tamerj1 · Accepted solution]

@Dbrunts0888_ 
Here is the sample file with the report https://www.dropbox.com/t/TnKpHp22hgLlkqQJ
First of all I have to say that working with Power Pivot is not a pleasant experience 🙂
Your whole subject is about building the correct data model. As you can see below I have added a Calendar Table (can be created in PowerPivot with one click). Also I have created a separate Branch Table that contains the unique list of names and numbers of all branches. Other tables should have the required column for building the correct relationship (either date or branch number or sometimes both)

Once your data model is set, the rest is simple. You Net Margin by Branch/Date measure is

Total Hours Worked :=
SUMX (
    CROSSJOIN ( VALUES ( Branches[BRANCH] ), VALUES ( 'Calendar'[Date] ) ),
    CALCULATE (
        (
            SUM ( HoursSpend[Hours] )
                + DIVIDE (
                    ( SUM ( HoursSpend[chmisc1] ) + SUM ( HoursSpend[chmisc1] ) ),
                    VALUES ( TravelHours_Charge[CHARGE] )
                )
        )
            * VALUES ( MarginPH[MARGIN] )
    )
)

This is how your report looks like. Must be interested in other detailed reports, please feel free to clarify your requirement so I can modify.

Please let me know if you have any further requirement.

[Dbrunts0888_]

Thank you for your time and assistance with this. I will review and check up on the DAX measure you have used.