Calculate LTIFR & TRIFR for multiple companies
Hi Community, I need to be able to show on a dashboard the calculated LTIFR (Lost Time Injury Frequency Rate) & TRIFR (Total Recordable Injury Frequency Rate) rate for my own company as well as those of our subcontractors. My company is represented as 'JKL4' and needs to be separate; all other companies should have a combined rate. I also need this to be on a rolling 12-month basis, so the rate for Jan 2024 will take into account the hours worked and injuries recorded from Feb-23 to Jan-24, the Feb-24 will be from Mar-23 to Feb-24 and so on and so forth The data is coming from an online SharePoint list that has a lot of columns, but I only need to use six for the calculation Month Contractor Hours LTI (Lost Time Injury) RDI (Restriced Duty Injury) MTI (Medical Treatment Injury) LTIFR calculation (LTI only) TRIFR calculation (LTI, RDI, and MTI) I have included a link (link no longer available) the data I will be using. Only the contractor names have been changed; all other data is correct The parent table name is 'Tb_HSEQ_Stats'
Other options for calculating margin categories by product and Year-Month
I have a business case to calculate revenue and margin for products within a certain margin range by year and month. For example, product category "A" has $10,000 sales in January 2025. In category A, $4,000 worth of products were under 10% margin. I started by creating a calculated table so I can calculate margin % by product and year month, then assigning a category. I think this will work for my use case, but I am afraid that the calculated table will reduce performance and if the semantic model is moved to Fabric, we won't be able to use DirectLake mode if a calculated table exists. Is there a way to achieve a similar result using measures? Below is the expression I used to create the calculated table. I also included a screenshot of the end result I am trying to achieve for reference. Below the table shows the % of revenue for products below 10% margin : The below DAX expression is what I am using to create a calculated table: VAR ProductsByYearMonth = SUMMARIZE ( 'Global Sales Invoicing', ProductDimExtended[Product_Dim_dKey], LegalCustomerDim[Customer_Dim_dKey], Time[YearMonth] ) VAR MarginByYearMonthProduct = ADDCOLUMNS( ProductsByYearMonth, "@ProdSales",[TotalInvoicedLineValue_USD], "@ProdCost",[TotalAdjustedCost_USD], "@ProdMargin",[MarginAdjusted_USD], "@ProdMargin%",[MArgin%Adjusted_USD], "@MarginCategory", SWITCH( TRUE, [TEST_Margin] <= 0.1, "Low", [TEST_Margin] <= 0.2, "Medium", "High" ) ) VAR Result = SELECTCOLUMNS ( MarginByYearMonthProduct, "ProductKey", ProductDimExtended[Product_Dim_dKey], "CustomerKey", LegalCustomerDim[Customer_Dim_dKey], "YearMonth", Time[YearMonth], "ProductSales", [@ProdSales], "ProdMargin",[@ProdMargin], "ProdMargin%", [@ProdMargin%], "MarginCategory", [@MarginCategory] ) RETURN Result
Issue with Calculated table with User's Selection
I have an excel table that looks like this: Financial Year VAT Number Sector Net Sales EBITDA Inventories 2019 100 1 10000 9000 800 2019 200 2 12000 9500 900 2020 100 1 15000 10000 1000 2020 200 2 16000 10000 1000 2021 100 1 17000 13000 450 2021 200 2 18000 14000 1200 2022 100 1 19000 15000 800 2022 200 2 20000 16000 800 2023 100 1 21000 17000 1200 2023 200 2 22000 18000 1200 ***I have a lot more columns related to the companies' balance sheet items, however I am simplifying it in this example. I imported the data to PowerBI and inserted a matrix which shows the following: Financial Year, Sum of Net Sales, Sum of EBITDA, Sum of Inventories, etc. I added 2 slicers: one that filters the years that appear in my matrix ('Sheet1'[Financial Year]), and one that filter the sector ('Sheet1'[Sector]). I created a calculated table, which contains one column with the VATs that appear in a Common Sample. This Common Sample refers to VATs who appear both in 2022 AND 2023, and have non-zero and non-blank Net Sales in both of those years. If a VAT does not meet this criteria, its value is blank in the calculated table. This table is connected to the original table (Sheet1) via a One-to-many relationship that links 'Net Sales CS 2022-2023'[VALID NET SALESVAT] to 'Sheet1'[VAT Number]. I input this calculated table in a slicer, selected all values apart from the blank ones, and this correctly filters my matrix and shows the Sum of Net Sales, EBITDA, etc, for the filtered common sample. The type of this table is the following: Net Sales CS 2022-2023 = VAR VATs2022 = FILTER( ALL('Sheet1'), 'Sheet1'[Financial Year] = 2022 && NOT(ISBLANK('Sheet1'[Net Sales])) && 'Sheet1'[Net Sales] <> 0 ) VAR VATs2023 = FILTER( ALL('Sheet1'), 'Sheet1'[Financial Year] = 2023 && NOT(ISBLANK('Sheet1'[Net Sales])) && 'Sheet1'[Net Sales] <> 0 ) RETURN DISTINCT( INTERSECT( SELECTCOLUMNS(VATs2022, "VALID NET SALES VAT", 'Sheet1'[VAT Number]), SELECTCOLUMNS(VATs2023, "VALID NET SALES VAT", 'Sheet1'[VAT Number]) ) ) I need help with the following query: Instead of creating a calculated table for the common sample of 2022 and 2023, I would like a calculated table with a dynamic selection of years of Common Sample, based on the users' selection. For example, a user might select the Net Sales Common Sample between 2020, 2021, and 2022. This calculated table (or column, or measure) will be added in a slicer, so that I can filter my matrix and showcase the Sum of Net Sales, EBITDA, Inventories, etc, ONLY FOR the Common Sample. IMPORTANT: I don't want to create separate measures for each and every column (which means balance sheet item), as there are too many of them in my data. I created a disconnected table that allows the user to select the years for which he/she wants to compute the common sample. I believe that this should be somehow to the calculated table, but not to the original table. YearSelection = DISTINCT('Sheet1'[Financial Year]) However, I didn't have any luck with different tries of creating a calculated table, measure, or column based on my query, SO I WOULD REALLY APPRECIATE YOUR HELP 🙂. EXTRA QUERY: The user might want to select a different column than the Net Sales one to find the Common Sample of the VATs for selected years.
Two dropdown menus to select two columns and calculate the difference/correlation
Hi, I am looking to create two dropdown menus to select two arbitary persons from the data set and calculate the difference in each day or the correlation. The original dataset in in wide format. Mindful that it is not the ideal format in Power BI, I have unpivot it into a long format. Day Anne Bill Charles David Evan 1 1 2 2.5 3 3 2 2 3 2.5 2 1 3 3 4 2.5 1 2 However, I struggled to create a calculate the score difference between two person in each day. I tried to use do so by using NATURALJOIN and SELECTEDVALUE, but SELECTEDVALUE always return empty instead of what is selected in the dropdown menu. Please find below the link to my Power BI file https://limewire.com/d/997fa681-3d66-4657-a310-6a6901ef5466#iQGXUyBikm-_s5Ox-hS2APKQ5G5LUEo7NrGi5-r29kM Can you point me to the right direction please? Thanks a million.Calculate in measure at column won't filter other objects
Hi! I placed this measure in "values" on a Matrix: measure = CALCULATE(sum(Fact[Totalsum]),Fact[type] = "D") But when i select anything within this matrix it will filter all objects from the model excluding Fact[type] = "D" and showing the absolute total (Measure total is selected) I also tried using SumX but didn't work: measure = SUMX(FILTER(Fact,Fact[type] = "D"),Fact[Totalsum]) Why could this be?
Dax help- Take value from multiple columns and create single column with multiple rows
Afternoon all, I'm looking for a little help on a novel situation I haven't dealt with before. The scenario: I have a dataset of invoice data for multiple companies. Depending on the company, the invoice needs to have different summary categories. I have a table relating company to invoice type, and another table with Invoice type to what categories it needs to include. The problem: I can't get the categories to display as rows in a table visual ( Once I have that figured out, totaling by category should be easy) I assume this would probably be a calculated table...but I was struggling with my current dax knowledge Thanks for your help in advance!! Data model relationships Example Data: Company Invoice Type A 1 B 2 C 1 D 6 E 2 F 1 Invoice Type Row 1 Row 2 Row 3 1 Category 1 Category 2 Category 3 2 Category 2 Category 4 6 Category 5 Invoice ID Company Amount 10000 A 53045 100002 B 40154 190004 A 45379 280006 D 71394 370008 E 54946 460010 F 13384 550012 F 81174 640014 B 11641 730016 C 84162DAX new Calculated table
Hi i want to create a new calculated table that get me the follwoing results: Flights handled Main 1,2,3,4,7 A 3,4 B 1,2,6 C 6,7,8,9,10 D 4,5,9 E 8,9,10 F and i have two tables one called flights and has these 4 columns: Flight number, fromtopair, from, to and the other table called Airports have two columns Main, sub. i want the new calculated column to get the flight numbers that the main airport or it's sub has handled. when the main airport or it's sub airport are one of the From or To column then we add the flight number to the main airport and the result should be like the table above. how can i do that in dax ?
lookup through multiple tables
Hi people, I hope you can help me with a challenging task in making a calculated table in Power BI: I have a data model loooking like this: My challenge is an effort to make a calculated table looking like this: my problem is, that in order to consolidate "Product Name" and "Customer Name" in the same table, I need to look them up through the Sales (Fact) table. I have made an attempt in the following DAX script, where I start out with the use of a SUMMARIZE() function to build a table on the "Product"-attributes. But I can't figure out how to add the "Customer"-attributes to the table, when the relation between the two goes thorough the "Sales"-table? This is my DAX code: Table = SUMMARIZE('D_Product','D_Product'[Product Key],'D_Product'[Product Name], "Customer_key", VAR RelatedValues_table_prod = FILTER( RELATEDTABLE('F_Sales'), VALUE('F_Sales'[PRODUCT KEY]) = VALUE(RELATED('Product'[PRODUCT_KEY])) ) VAR RelatedValues_table_cust = FILTER( RELATEDTABLE('F_Sales'), VALUE('F_Sales'[CUSTOMER KEY]) = VALUE(RELATED('D_Customer'[CUSTOMER KEY])) ) RETURN MAXX(RelatedValues_table_cust, 'F_Sales'[CUSTOMER_KEY]), "Product key", VAR RelatedValues_table_prod = FILTER( RELATEDTABLE('F_Sales'), VALUE('F_Sales'[PRODUCT KEY]) = VALUE(RELATED('D_Product'[PRODUCT_KEY])) ) VAR RelatedValues_table_cust = FILTER( RELATEDTABLE('KPI Sell In'), VALUE('KPI Sell In'[CUSTOMER_KEY]) = VALUE(RELATED('Customer'[CUSTOMER_KEY])) ) RETURN MAXX(RelatedValues_table_prod, 'KPI Sell In'[PRODUCT_KEY]) ) To be honest, I am not quite sure how to cóntinue from here? Can it be done? Any help or guidance is much appreciated. Thanks. Br, JayJay0306
Filter calculated table using slicer
Hello, I would like to be able to filter a calculated table using slicers on my PBI report. I use a master table that contains several data items (reception dates, value used for slicers, etc.) and a date table to show the number of applications received by week. I created a relationship between the reception date and the date table, a measure to calculate the number of applications received and added a custom column (CFP 2023 by week) to my date table to define the week the application was received. After that, I created a calculated table to display the number of applications received per week. My table is working correctly but I'd like to be able to use the information in the master table to filter the information in the calculated table. Could someone help me make this work? Here is the details of my calculated table: Application Received by Week = ROW( "Week", "Application Received", "1", CALCULATE([Number of Projects], 'Date'[CFP 2023 by Week] = "Week 1"), "2", CALCULATE([Number of Projects], 'Date'[CFP 2023 by Week] = "Week 2"), "3", CALCULATE([Number of Projects], 'Date'[CFP 2023 by Week] = "Week 3"), "4", CALCULATE([Number of Projects], 'Date'[CFP 2023 by Week] = "Week 4"), "5", CALCULATE([Number of Projects], 'Date'[CFP 2023 by Week] = "Week 5"), "6", CALCULATE([Number of Projects], 'Date'[CFP 2023 by Week] = "Week 6"), "7", CALCULATE([Number of Projects], 'Date'[CFP 2023 by Week] = "Week 7"), "8", CALCULATE([Number of Projects], 'Date'[CFP 2023 by Week] = "Week 8"), "9", CALCULATE([Number of Projects], 'Date'[CFP 2023 by Week] = "Week 9"), "Total", CALCULATE([Number of Projects], NOT(ISBLANK('Date'[CFP 2023 by Week]))) ) Table in the report: Thanks a lot for your help!Return sum of monthly values only on the first row of each month
I have the following code... Tbl = VAR Datas_IR_Long = SUMMARIZECOLUMNS( cIRLong[Data] ) VAR Datas_IR_Short = SUMMARIZECOLUMNS( cIRShort[Data] ) VAR Datas_IR_Long_Short = DISTINCT( UNION( Datas_IR_Long, Datas_IR_Short ) ) VAR Datas_IR_Long_Short_Result = ADDCOLUMNS( Datas_IR_Long_Short, "@Result_Apur_Long", VAR Data_Ref = [Data] RETURN CALCULATE( SUM( cIRLong[Resultado apurado no mês] ), cIRLong[Data] = Data_Ref ), "@Result_Apur_Short", VAR Data_Ref = [Data] RETURN CALCULATE( SUM( cIRShort[Resultado apurado no mês] ), cIRShort[Data] = Data_Ref ) ) RETURN ADDCOLUMNS( Datas_IR_Long_Short_Result, "@Result_Cons", VAR Data_Ref = [Data] RETURN SUMX( FILTER( Datas_IR_Long_Short_Result, FORMAT( [Data], "YYYYMM" ) = FORMAT( Data_Ref, "YYYYMM" ) ), [@Result_Apur_Long] + [@Result_Apur_Short] ) ) ... that produces the following table: However, I can only male my goal column "@Result_Cons" repeat the monthly sum of "@Result_Apur_Long" + "@Result_Apur_Short" for every row under the same respective month. I need something like the column on the far right (which is just an edited image for illustration purposes), showing each month's sum only under the first row of every month, returning the remaining rows as blanks. I tried to solve it by inserting an IF statement with the following piece of code, but I get an error saying that the function LASTDATE cannot be used under ADDCOLUMNS or SUMMARIZE functions. ... VAR Datas = SELECTCOLUMNS( Datas_IR_Long_Short_Result, "@Data", [Data] ) IF( MONTH( Datas ) <> CALCULATE( MONTH( LASTDATE( Datas ) ), FILTER( Datas_IR_Long_Short_Result, [Data] < Data_Ref ) ), ... How can I get that done?
