Create a time sensitive matrix using measure
Hi All, I have a dataset that includes the following columns: • Client Name: The name of the client company. • Client Representatives: The names of representatives associated with the client (could be one or multiple). • Session Name: The name of the session. • Session Status: Indicates whether the session was attended or not (Attended or Not Attended). I created the following measures: 1. Attendance Rate: This measure calculates the attendance rate per company. It is computed by dividing the total number of company representatives who attended sessions by the total number of representatives invited to sessions. 2. Unique Attendees Per Company: This measure counts the unique representatives who attended at least one session for each company. Problem and Solution The challenge is that small companies are invited to fewer sessions and can easily achieve a 100% attendance rate if they attend all their sessions. In contrast, large companies, invited to many sessions, may attend a large number of them but appear to have a lower attendance rate due to the sheer volume of sessions they are invited to. This could misrepresent the data, as small companies would seem to perform better purely because of their smaller scale. Your explanation is mostly clear, but there are a few places where it can be clarified further to make your intent and approach more precise. Here’s a revised version of what you’re trying to say: Now, I want to use the Weighted Attendance Rate measure to segment companies into five buckets (segments). To do this: 1. I calculate the range of weighted attendance rates (i.e., Max - Min) and divide it into five equal intervals. 2. Based on these intervals, companies will be assigned to one of five segments, with Segment 1 having the lowest rates and Segment 5 having the highest. Additionally, I want to create five buckets (segments) for the Number of Unique Attendees per Company using a similar approach: 1. I calculate the range of unique attendee counts (i.e., Max - Min) and divide it into five equal intervals. 2. Based on these intervals, companies will be assigned to one of five segments, with Segment 1 having the smallest number of attendees and Segment 5 having the largest. • Circular Dependency Error: When I tried to crens for the segments, I encountered a circular dependency error. • Using Measures: I then tried to create measures and an isolated table for the segmentation logic, but I was still unable to achieve the desired output. I need help understanding how to correctly implement this segmentation logic without running into circular dependency issues, and how to structure my data/model effectively to achieve the desired segmentation. the output results should look like this Weighted attendance segments 0-100 attendees 100-200 attendees 200-300 attendees 300-400 attendees 0-25 5 8 33 77 25-50 2 5 12 18 50-75 3 3 6 32 75-100 7 9 2 54
Dimension table to Filter Measure - Data Model Help?
I think this is simple, but I am new to DAX and data modeling. I am using Excel & Power Query. I have 2 tables: RS_Cases (fact table) and Date_Table (dimension table). The goal is to create a power pivot with slicers for dates and programs to track backlog cases. The following measure calculates the total backlog: Backlog:=CALCULATE( COUNTROWS(RS_Cases), FILTER( ALL(RS_Cases), (RS_Cases[Due By]<=MAX(Date_Table[Date]) && RS_Cases[Status]="OPEN") || (RS_Cases[Due By]<=MAX(Date_Table[Date]) && RS_Cases[Entrance Date]>Max(Date_Table[Date])) || (RS_Cases[Due By]<=MAX(Date_Table[Date]) && ISBLANK(RS_Cases[Entrance Date]) && RS_Cases[Status]="CLOSED" && RS_Cases[Status Change Date]>MAX(Date_Table[Date])) ) ) But now I want this measure to give me the total backlog based on the program (field in RS_Cases) selected in the pivot table. I tried building a 2nd dimension table for Program, creating a relationship with the fact table. Then tried to alter the measure above like this: Backlog:=CALCULATE( COUNTROWS(RS_Cases), USERELATIONSHIP(Programs[Program],RS_Cases[Program]), FILTER( ALL(RS_Cases), (RS_Cases[Due By]<=MAX(Date_Table[Date]) && RS_Cases[Status]="OPEN") || (RS_Cases[Due By]<=MAX(Date_Table[Date]) && RS_Cases[Entrance Date]>Max(Date_Table[Date])) || (RS_Cases[Due By]<=MAX(Date_Table[Date]) && ISBLANK(RS_Cases[Entrance Date]) && RS_Cases[Status]="CLOSED" && RS_Cases[Status Change Date]>MAX(Date_Table[Date])) ) ) I used the program field from the dimension table when I built the pivot, but the measure does not change when I filter the pivot table by program. Where am I going wrong here?Relationship issues showing BLANK values
Hi All, I built a quick model with a lot of rows (10 million). The model analyses the YOY variances per product product group. A product reference is linked to a product group. In some cases, the data might have mistakes and 1 reference is linked to multiple product groups. For some reasons, I can see some "(blank)" values and I don't understand why. When I click on the revenue, it shows nothing as if it is empty. Could you please help? Thanks so much for your assistance!Suitable DAX for model having two fact tables and multiple dim tables
Hi, Requesting help on suitable DAX for creating matrix visual as below using two fact tables. FactTable1 having ReasonCode ingested at various time windows for several devices FactTable2 having Value ingested at various time stamps for several meters DimTable2 has the description for Reasons There is a bridge table DimTable1 mapping deviceID and meterID and has the description of machine name as well. Also there are two Dim tables one for Date (DimTable3) and another for time (DimTable4). Data model proposed as below.(PBIX file also enclosed) Required basic matrix visual giving aggregated total of values (FactTable2) output sliced based on date window and machine name as below: I had used calculated columns and got the output but since data size is huge it takes processing time and gets hanged. I need a suitable DAX which will aggregate based on relation ship only for my requirement.
Dax for Bonus Threshold
Hi All We give our customers a bonus based on various conditions. Those conditions are different per customer but the same logic. example: customer 1: if revenue < 1M€, no bonus. Between 1M and 2M, 5% of sales as bonus, above 2M, 7% of bonus customer 2: if revenue < 2M€, no bonus. Between 2M and 5M, 7% of sales as bonus, above 5M, 8% of bonus 1% of sales on product A is more than 1 M 2% on the growth (revenue current year - revenue last year) if growth is 10%, 3% if the growth is between 10 and 15% I am looking at a way to build a DAx formula and why not a data model. do you have any idea ?
Combining two tables in a pivot chart / calculating SUM table 2 based on row label of table 1
Hey everybody, I assume this shouldn't be a big deal but I've already spend hours on this and I'm not able to figure it out on my own. I've two tables which I'd like to combine in a pivot chart, including claculations between both tables. I'd like to have a pivot chart, showing: x-axis: products (working ✔️) y-axis (main): Distinct count of serial numbers of serviced products (working ✔️) y-axis (secondary): Values on x-axis (main) in relation to build products (not working ❌) The example file can be found here: example_combine_two_tables_in_pivot_chart.xlsx Input data (the two tables) - worksheet "Input_data" table "tbl_build_products" - which products where build in which year and in which amount (multiple entries, not aggregated by year / product) Product build year amount of products build A 2020 1 B 2020 1 C 2020 2 A 2020 2 B 2020 3 C 2021 3 A 2021 4 B 2021 4 C 2021 5 A 2021 5 table "tbl_serviced_products" - which products where serviced when (multiple entries per product and serial number possible) Product serial number service year A S1 2020 B S2 2020 C S3 2020 A S1 2021 B S2 2021 C S6 2021 A S7 2020 B S8 2020 C S9 2020 A S10 2020 B S2 2021 C S12 2021 A S13 2021 B S14 2021 C S15 2021 A S16 2020 B S17 2020 C S18 2021 current data model I've marked the columns which contain identical values. How it looks like at the moment - worksheet "Pivot_table_chart" blue bars = Distinct count of serial numbers of serviced products (working ✔️) orange bars = Instead, here I'd like to show the values on x-axis (main) in relation to build products (not working ❌). Currently, the orange bars are representing the sum of build products in total, not according to the corresponding row labels. Underlying, there's a measure which is currently summing column "amount of products build" in table "tbl_build_products". This measure looks like this: =SUMX(VALUES(tbl_build_products[amount of products build]);[Sum of amount of products build]) What I'm trying to achieve (next steps) Change measure in a way that I'm getting the sum of column "amount of products build" in table "tbl_build_products" but using the row labels as a kind of filter Calculate the percentage of "Distinct Count of serial number" in relation to all build products of the same product (see Nr. 1) Instead of the orange bars, show the percentage value of Nr. 2 as a line diagram on the secondary y-axis I'm open to other ideas to achieve my goal. I'm looking forward to your assistance! Thanks 🙂 edit I've created another table where I've used regular excel formulas to show what I'm trying to achieve in the end. My problem is that the real dataset is more complicated and the helper column "helper_column_count_distinct" I've inserted into "tbl_serviced_products" (worksheet Input_data) only works as long as all entries are valid. As soon as I start filtering those entries in a pivot chart using another column, the values in the helper column wouldn't be correct anymore. That's why I'm trying to make it work in a way where I can use the full power of a pivot chart. Here is the updated example file: example_combine_two_tables_in_pivot_chart_2.xlsx Input tbl_service_products with helper column Product serial number service year helper_column_count_distinct A S1 2020 0,5 B S2 2020 0,3 C S3 2020 1 A S1 2021 0,5 B S2 2021 0,3 C S6 2021 1 A S7 2020 1 B S8 2020 1 C S9 2020 1 A S10 2020 1 B S2 2021 0,3 C S12 2021 1 A S13 2021 1 B S14 2021 1 C S15 2021 1 A S16 2020 1 B S17 2020 1 C S18 2021 1 Mockup end result using regular excel formulas - worksheet "input_combined" tbl_input_combined product build products amount total serviced products distinct serial number rate of serviced products A 12 5 42% B 8 4 50% C 10 6 60% This results in this pivot table / chart (that's basically what I'd like to achieve without using a helper column and regular forumas):previous week calculation repeating over empty dates weekly to daily relationship
I have the below data model. Where the fact table is weekly and calendar is daily. When I calculate the previous week value it adds all the dates from the calendar rathen than keeping only dates in the fact table. PREVIOUSWEEK Value WDT = VAR _Today = SELECTEDVALUE(Lookup_Calendar[Date]) VAR _WOT = WEEKNUM( _Today, 1 ) -- Number 2 determines that the week begins on Monday. VAR _LW = _WOT - 1 RETURN CALCULATE( SUM( Data[Units]), FILTER( ALLSELECTED( Lookup_Calendar ), Lookup_Calendar[Week Of Year] = _LW && Lookup_Calendar[Year] = YEAR(_Today ) ) ) pbixfile
