User Profile
JenWilson
Helper II
Joined 3 years ago
User Widgets
Contributions
Re: DAX Trend line - need two lines in one formula
lbendlin I get why the requirements are being asked for. The owner of the report is looking to see trends not only as a whole but comparing one department to the next. The screen shots that I provided were only a few days of the data. So when the owner of the report wants to compare say month over month, seperating the departments wouldn't help the user. Thanks for trying!Re: DAX Trend line - need two lines in one formula
Anonymous This isn't working. My "dots" represent data points of two trend line measures (one for each dept) and the formula you provided, doesn't allow the use measures in the calculation. I believe the problem that I am having is that the X axis consits of the date and then the department so it only makes sense that these two measures only show dots (as each measures needs to skip over the department that is not associated with it). If I use a single measure for both departments, I get a solid line, but it connects each data point for each department which causes the jagged line. P.S. I have no content in the column legend field for my stacked bar chart. Let me know if you have any other ideas. Thank you!Re: DAX Trend line - need two lines in one formula
lbendlin , I have individual measures also, but the charting only gives me dots vs. a nice line for each department as shown below in the first screen shot. I suspect this is because of the sort order of the two X axis which is Date and Dept. If I reverse the sort order to have Dept then Date, I get the solid line. See the second screen shot. But again, the requirement is to have the data grouped by each date and dept together. So for the individual measures, is there a way to show these dots as a solid line?DAX Trend line - need two lines in one formula
The Dax trend line formula I am using is working great, however, when visualized within a stacked column chart with multilple X axis items (Sorted first by date and second by Dept), it appears jagged (see screen shot 1). If I flip the drill down order of the X axis (putting Dept first and Date second), I get a better visual (still connected, but one section for Dept A and one for Dept B - see screen shot 2). The requirements I've been given would best be represented by the first screen shot. With that... Is there anyway that I can modify the formula below so that this one formula would produce two trendlines for each department? (The "Known[Z]", [Department] portion of the formula below). Job Transition Time Trend Dec. Hrs Measure Eagle 1 & 2 = VAR Known = Filter( SELECTCOLUMNS( ALLSELECTED ('Calendar'[Date]), "Known[X]", 'Calendar'[Date], "Known[Y]", [TotalHours], "Known[Z]", [Department] ), AND(AND(NOT(ISBLANK(Known[X])), NOT(ISBLANK(Known[Y]))),Known[Z])) VAR Count_Items = COUNTROWS(Known) VAR Sum_X = SUMX(Known, Known[X]) VAR Sum_X2 = SUMX(Known, Known[X]^2) VAR SUM_Y = SUMX(Known, Known[Y]) VAR SUM_XY = SUMX(Known, Known[X] * Known[Y]) VAR Average_X = AVERAGEX(Known, Known[X]) VAR Average_Y = AVERAGEX(Known, Known[Y]) VAR Slope = DIVIDE(Count_Items * SUM_XY - Sum_X * SUM_Y, Count_Items * Sum_X2 - Sum_X^2) VAR Intercept = Average_Y - Slope * Average_X Return SUMX(DISTINCT(Calendar[Date]), Intercept + Slope * Calendar[Date])Only Show rows where there are matching values
Below is an example of a table visual I am working on in PBI. I am trying to display the tables where the data is coming from (there are 3 tables which do have relationships. Customers table to Sales Table (one to many), Product Table to Sales Table (one to many). What I am looking to do, is only display a single row when the values from the column "Product Table Level" match the values from the Customer Table Level". In the screen shot below, the first table represents how the data is currently being displayed. The second table is the desired result. Is there a dax formula that I can use to filter this table down to the desired result? Thank you!
