topic Re: Measures to remove columns… in DAX Commands and Tips

Measures to remove columns…

Dbrunts0888_ — Wed, 23 Feb 2022 20:26:10 GMT

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?

Re: Measures to remove columns…

VahidDM — Thu, 24 Feb 2022 02:16:32 GMT

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/

Re: Measures to remove columns…

Dbrunts0888_ — Thu, 24 Feb 2022 08:51:52 GMT

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

Re: Measures to remove columns…

tamerj1 — Thu, 24 Feb 2022 08:50:11 GMT

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

Re: Measures to remove columns…

Dbrunts0888_ — Thu, 24 Feb 2022 08:52:43 GMT

Hi tamerj1

I just added this in as a reply

Re: Measures to remove columns…

tamerj1 — Thu, 24 Feb 2022 09:13:00 GMT

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

Re: Measures to remove columns…

Dbrunts0888_ — Thu, 24 Feb 2022 09:19:06 GMT

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

Really appreciate your time.

Re: Measures to remove columns…

tamerj1 — Thu, 24 Feb 2022 09:24:32 GMT

Dbrunts0888_
Upload to WeTransfer for example and send the link

Re: Measures to remove columns…

Dbrunts0888_ — Thu, 24 Feb 2022 09:26:53 GMT

tamerj1 has this worked?

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

Re: Measures to remove columns…

Dbrunts0888_ — Thu, 24 Feb 2022 09:31:00 GMT

tamerj1 has this worked?

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

Re: Measures to remove columns…

tamerj1 — Thu, 24 Feb 2022 10:12:26 GMT

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!

Re: Measures to remove columns…

Dbrunts0888_ — Thu, 24 Feb 2022 10:23:41 GMT

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.

Re: Measures to remove columns…

tamerj1 — Mon, 28 Feb 2022 13:06:41 GMT

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.

Re: Measures to remove columns…

Dbrunts0888_ — Mon, 28 Feb 2022 13:13:23 GMT

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