Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have been given a dataset and I have been asked to create a measure table. below is the table and the measure list I have is this.... now please someone suggest me how can i create a measure table in the script
Revenue |
Gross Profit |
PBIT |
ROCE |
Working Capital |
Account Receivables |
Account Payable |
Inventory |
Total Cost |
COGS |
Operation Expenses |
Manpower Cost |
Net Profit |
Operation Cost |
Transportation Cost |
Days Sales Outstanding |
Inventory Days |
Sr. No | Business Unit | Division | Plant | Material Group | Product2 | Vendor_Customer | Purchasing Organization | Bucket | Date | Measure Name | Value | Goal |
1 | BU 1 | Div 1 | Plant 1 | Mrp Grp 1 | Product 1 | Customer 1 | Org 1 | 01-01-2009 | Revenue | 1850 | 3975 | |
2 | BU 2 | Div 2 | Plant 1 | Mrp Grp 1 | Product 2 | Customer 2 | Org 2 | 01-01-2009 | Revenue | 5656 | 4621 | |
3 | BU 3 | Div 3 | Plant 1 | Mrp Grp 1 | Product 3 | Customer 3 | Org 3 | 01-01-2009 | Revenue | 5434 | 1627 | |
4 | BU 1 | Div 4 | Plant 1 | Mrp Grp 2 | Product 4 | Customer 4 | Org 4 | 01-01-2009 | Revenue | 2947 | 2445 | |
5 | BU 2 | Div 5 | Plant 2 | Mrp Grp 2 | Product 5 | Customer 5 | Org 5 | 01-01-2009 | Revenue | 1974 | 3287 |
Sr. No | Business Unit | Division | Plant | Material Group | Product2 | Vendor_Customer | Purchasing Organization | Bucket | Date | Measure Name | Value | Goal |
1 | BU 1 | Div 1 | Plant 1 | Mrp Grp 1 | Product 1 | Customer 1 | Org 1 | 01-01-2009 | Revenue | 1850 | 3975 | |
2 | BU 2 | Div 2 | Plant 1 | Mrp Grp 1 | Product 2 | Customer 2 | Org 2 | 01-01-2009 | Revenue | 5656 | 4621 | |
3 | BU 3 | Div 3 | Plant 1 | Mrp Grp 1 | Product 3 | Customer 3 | Org 3 | 01-01-2009 | Revenue | 5434 | 1627 | |
4 | BU 1 | Div 4 | Plant 1 | Mrp Grp 2 | Product 4 | Customer 4 | Org 4 | 01-01-2009 | Revenue | 2947 | 2445 | |
5 | BU 2 | Div 5 | Plant 2 | Mrp Grp 2 | Product 5 | Customer 5 | Org 5 | 01-01-2009 | Revenue | 1974 | 3287 | |
6 | BU 3 | Div 6 | Plant 2 | Mrp Grp 3 | Product 6 | Customer 6 | Org 6 | 01-01-2009 | Revenue | 3905 | 2269 | |
7 | BU 1 | Div 1 | Plant 2 | Mrp Grp 3 | Product 7 | Customer 7 | Org 7 | 01-01-2009 | Revenue | 4786 | 2925 |
Not sure quite what you mean by "Create a measure table". You can just create a regular table with whatever measures you want. In your above example, it looks like a basic Sum(Revenue).
what about other measures ive many measures there, do i have to calculate different measures everytime using a resident load.
Typically something like this would be calculated on the front end, not in the script. Doing it in the script will your row count being 16 times higher, which isn't likely to be the best approach.
If you do it in script, you can join the two tables to create a Cartesian join, and then resident load adding the appropriate expressions using Pick(Match()) or nested If() statements.
yes I know this is not the best approach but I have been given this as an assignment, now I guess i will have to do this using the functions, like you said and create multiple tables for every measure.
Weird to get an assignment that specifies you have to do something in this exact manner, but if that's what you got, good luck I guess!
Yes, actually I have been Given a Power BI wireframe as the requirements are like
Tables that we need to use on this dashboard
Data Model
Expected Functionality
It feels like whoever wrote that has never used Qlik... not sure why anyone would insist on a weird data structure instead of designing for the required results. In any case, join the tables and then pick/match for the formulas in a resident load is the easiest approach for doing in script, probably.
Thank you for this