Total Sum of a column as a measure
In this table, I wasn 80Mil against all the machinetypes. I created the measure electricity total usuage like this: Electricity Total Usage = CALCULATE( SUM('Fact ResourceUsage'[Usage]), 'Fact ResourceUsage'[ResourceTypeKey] = 1, ALL('Fact ResourceUsage'[MachineTypeKey]) ) But its just giving the total consumption of that particular machine type
Automatically show data set for todays date
Hello Powerbi people, Hope your all well? 🙂 Would someone be able to kindly help me, I have made a dashboard please see image attached. "Budget" will automatcily feed from a table and so will the prebook and daycode. I have attached a image of the setup of the excel table which is in powerbi. How would you automaticly show each data set for "todays current date" if that is possible? Many thanks,Data Transformation
I am trying to replicate my excel calculations in Powerbi. Sheet 3 in https://drive.google.com/file/d/1c607JpoZPCWXfK9exJaTjGo4kU9oWdyD/view?usp=sharing and sheet 2 in https://drive.google.com/file/d/1E-k77iw7N58FFqX3lf2CBEDBoGFKUpq_/view?usp=sharing i devide to get desired results. I would like the same in Powerbi. To get the desired tables and column name changes i use this sql code: App Filter Categories select a.label as ‘App Category’ , b.label as ‘Menu’ , c.product_uid as ‘Product UID’ from app_menu_filters a left join app_menu_filters b on b.parent_uid=a.uid left join app_menu_filter_products c on b.uid=c.app_menu_filter_uid where a.status=‘A’ and a.level=1 order by 1,2,3; Sales and Product Data SELECT date(o.created_datetime) as Date, od.product_uid as ‘Product UID’, p.manufacturer Manufacturer, p.bmc BMC, p.brand Brand, od.label as SKUs, p.selling_unit as ‘Unit of Measure’, ROUND(if(od.amended_quantity is not null, od.amended_quantity, od.quantity)) as ‘Units Sold’, ROUND((if(od.amended_quantity IS NOT NULL, od.amended_quantity, od.quantity))*p.content,2) as ‘Sales Volume’, ROUND((if(od.amended_quantity is not null, od.amended_quantity, od.quantity))*od.price,2) as ‘Sales Value’ FROM order_detail od left join order o on od.order_uid=o.uid left join product p on od.product_uid=p.uid where o.status in (‘D’,1,2,3,4,5) and not od.label =‘Plastic Bag’ and date(o.created_datetime) >= ‘2020-04-01’ Problem is, i am not getting these sales data in my powerbi model when using the above sql script, as indicated here: https://drive.google.com/file/d/1E-k77iw7N58FFqX3lf2CBEDBoGFKUpq_/view?usp=sharing Some how my data for the app category disapear. What am i doing wrong? PBIX file with SQL code use https://drive.google.com/file/d/186vsjbqclSXB4m5LGwdp3agMQ6-63LDs/view?usp=sharing PBIX when i pull in the tables manually :https://drive.google.com/file/d/1HCoYlPrhBQKuvWb-TpOBbDBEnv0BV0Sj/view?usp=sharing
Complex formula to provide counts and accurate percentages
Hi All- I'm working on the intersection between two tables. One table is a list of employees. There is one line per employee, there is a region column, and a status column (active or inactive). The other table is a list of certifications. There is one line per certification, there is a platform column (i.e. scrum, c+), there is a name column (matching names on the other table), and a status column (active or inactive). I'm trying to create essentially a pivot table. The columns across the top are the platforms, and each platform has a count and a percentage (percentage of employees in that region, not total). The rows are the regions. This should only reflect active employees by country (using the first table as a reference). This is the formula I'm using right now, but it's providing inaccurate percentages and I can't figure out why: Percentage Certifications New = // get distinct count of employees on certifications list witn only active employees var individuals = DISTINCTCOUNT('Certifications Active Only'[Name]) // get count of active employees on employee list var total = COUNTAX('GlobalTeam (2)',[Status]="Active") // get count of employees by country !!ONLY one of the two options!! // OPTION 1 var countryTotal = COUNT('GlobalTeam (2)'[Country]) // OPTION 2 var countryTotal = COUNT('Certifications Active Only'[Country]) // get percentage of certified employees in the country var percentage = ROUND(((individuals/countryTotal)*100),0) // return all values for auditing purposes. Once complete only percentage will be returned return "percent"&percentage&"!total"&total&"!ind"&individuals&"!country"&countryTotal Option 1 returns the same countryTotal for every single row, including the total row. This is not correct. Option 2 returns a count of every certification line relevant to the platform, where multiple lines can reflect a single individual. The 61 in the screenshot below is more rows than we have employees with BP certifications. Here's a small example screenshot of the table reflecting option 2, for help explaining it. I can't do a distinct count on the names in the certifications list, because then numberator and denominator will be the result of the same function so everything will be 100%. Please help!! Thank you!
