User Profile
thomazinh
Helper I
Joined 4 years ago
User Widgets
Contributions
Show top category for each row
I'm trying to configure a report to show the top category (issueCause) for each row (projectName). Each categoryId is assigned to an issueCause. There are multiple different issueCauses and I want to filter out for BLANK() values. I was able to rank the issueCauses by number of categoryIds, but when I place it in a table to show the most frequent issueCause with the projectName, I recieve an error. The below measure does what I want when I put that and issueCause field into a table. It orders them accordingly. custom_freq_ranked_issue = VAR rankedCategory = RANKX ( ALLSELECTED ( table[issueCause] ), CALCULATE ( COUNT ( table[categoryId] ) ), , DESC, DENSE ) RETURN rankedCategory Below is an output of what I want to create. Sample data is linked below also. projectName most frequent issue cause projectNameBlue Design projectNameDaisy Design projectNameGreen Design projectNamePink Design Change projectNameRed Design projectNameYellow Design Example DataAPI multiple call to return all data (paging)
I don't have a lot of M Query experience, and I'm working on calling all data points for an API call. In order to get where I want, I have to make 2 seperate calls above this (asking for a list of all company projects and a second asking for those project's inspections). The goal is to get all checklist itmes from those inspections. How can I go through and page to get all data? My current M query looks something like this: ChecklistItems = Table.AddColumn( #"Filtered Rows1", "New", each Json.Document( Web.Contents( "https://api.WEBSITE.com/rest/v1.0/", [ RelativePath = "projects/" & Text.From([Project Id]) & "/checklist/list_items?filters[inspection_type_id]=98634", Query = [ per_page = "1000" ], Headers = [ Authorization = AuthToken ] ] ) ) ),DAX AVERAGEX total result not expected in matrix
I have a matrix where I'm sumarizing by Project and Discipline. The Adjusted Bid Factor values in the Matrix are reading from different What If sliders and displaying those selected results at the row level. I want the Totals in the Matrix to show the average value at Project 1 and Project 2 and finally for the Total. I expect Project 1's total to be 0.75. Project 2 to be 1.0, and the Total row to be 0.88. Below is my sample code displaying the Adjusted Bid Factor column using the AVERAGEX _Bid Factor (Selected) AVGX = //AVERAGEX('WbsCode Forecast',[_Bid Factor (Selected)]) IF ( HASONEFILTER('WbsCode Forecast'[WbsCode.Discipline.name] ), 'WbsCode Forecast'[_Bid Factor (Selected)], IF( HASONEFILTER(Jobs[JobName] ), AVERAGEX('WbsCode Forecast', 'WbsCode Forecast'[_Bid Factor (Selected)]), CALCULATE( AVERAGEX( ALLSELECTED(Jobs[JobName] ), 'WbsCode Forecast'[_Bid Factor (Selected)]) ) ) ) Below is my Bid Factor Selected DAX, _Bid Factor (Selected) = // Pre-req DAX for the _Bid Factor (Selected) AVGX measure. // Displays the What If selected value for each of the corresponding Disciplines VAR ShowSelected = SWITCH( TRUE(), CONTAINSSTRING( SELECTEDVALUE('WbsCode Forecast'[WbsCode.Discipline.name]),"PLI"), SELECTEDVALUE('PLI Bid Factor'[PLI Bid Factor]), CONTAINSSTRING( SELECTEDVALUE('WbsCode Forecast'[WbsCode.Discipline.name]),"PLF"), SELECTEDVALUE('PLF Bid Factor'[PLF Bid Factor]), CONTAINSSTRING( SELECTEDVALUE('WbsCode Forecast'[WbsCode.Discipline.name]),"PFI"), SELECTEDVALUE('PFI Bid Factor'[PFI Bid Factor]), CONTAINSSTRING( SELECTEDVALUE('WbsCode Forecast'[WbsCode.Discipline.name]),"PFF"), SELECTEDVALUE('PFF Bid Factor'[PFF Bid Factor]), CONTAINSSTRING( SELECTEDVALUE('WbsCode Forecast'[WbsCode.Discipline.name]),"SMI"), SELECTEDVALUE('SMI Bid Factor'[SMI Bid Factor]), CONTAINSSTRING(SELECTEDVALUE('WbsCode Forecast'[WbsCode.Discipline.name]),"SMF"), SELECTEDVALUE('SMF Bid Factor'[SMF Bid Factor]), 1 ) Var Result = IF(ISINSCOPE('WbsCode Forecast'[WbsCode.Discipline.name]),ShowSelected,0) //Displays the rollup value for the Bid Factor starting at the WBS level Return Result Can someone help me remedy the Total values in the matrix to show the average instead of what it currently is?Averagex not working to calculate average in matrix
I have a matrix where I am expecting to get an average value for the Total rows in the Adjusted Bid Factor column. The Adjusted Bid Factor column is using a What If value that I'm referencing called _Bid Factor (Selected). The matrix row structure is (lowest to highest) Discipline Name to Project Name. I tried using HASONEFILTER but was unsuccessful. I'm doing something similar with SUMX on Forecasted Actual Cost and have no issues. On the above matrix, I would expect the top total for the Project to be the 0.75 (=(1.00+0.50)/2) but it is returning 0.63. For the Total row, I would expect 0.88 (=(1.0+1.0+1.0+0.5)/4). The _Bid Factor (Selected) DAX doesn't give me any issues, _Bid Factor (Selected) = // Pre-req DAX for the _Bid Factor (Selected) AVGX measure. // Displays the What If selected value for each of the corresponding Disciplines VAR ShowSelected = SWITCH( TRUE(), CONTAINSSTRING( SELECTEDVALUE('WbsCode Forecast'[WbsCode.Discipline.name]),"PLI"), SELECTEDVALUE('PLI Bid Factor'[PLI Bid Factor]), CONTAINSSTRING( SELECTEDVALUE('WbsCode Forecast'[WbsCode.Discipline.name]),"PLF"), SELECTEDVALUE('PLF Bid Factor'[PLF Bid Factor]), CONTAINSSTRING( SELECTEDVALUE('WbsCode Forecast'[WbsCode.Discipline.name]),"PFI"), SELECTEDVALUE('PFI Bid Factor'[PFI Bid Factor]), CONTAINSSTRING( SELECTEDVALUE('WbsCode Forecast'[WbsCode.Discipline.name]),"PFF"), SELECTEDVALUE('PFF Bid Factor'[PFF Bid Factor]), CONTAINSSTRING( SELECTEDVALUE('WbsCode Forecast'[WbsCode.Discipline.name]),"SMI"), SELECTEDVALUE('SMI Bid Factor'[SMI Bid Factor]), CONTAINSSTRING(SELECTEDVALUE('WbsCode Forecast'[WbsCode.Discipline.name]),"SMF"), SELECTEDVALUE('SMF Bid Factor'[SMF Bid Factor]), 1 ) Var Result = IF(ISINSCOPE('WbsCode Forecast'[WbsCode.Discipline.name]),ShowSelected,0) //Displays the rollup value for the Bid Factor starting at the WBS level Return Result Below is what I have for the _Bid Facotr (Selected) AVGX DAX, _Bid Factor (Selected) AVGX = //AVERAGEX('WbsCode Forecast',[_Bid Factor (Selected)]) IF ( HASONEFILTER('WbsCode Forecast'[WbsCode.Discipline.name] ), 'WbsCode Forecast'[_Bid Factor (Selected)], IF( HASONEFILTER(Jobs[JobName] ), AVERAGEX('WbsCode Forecast', 'WbsCode Forecast'[_Bid Factor (Selected)]), CALCULATE( AVERAGEX( ALLSELECTED(Jobs[JobName] ), 'WbsCode Forecast'[_Bid Factor (Selected)]) ) ) ) Below is my similar code for the Forecasted Actual Cost column, _Productive Labor Field (Forecasted Actual Cost) SUMX = IF ( HASONEFILTER ( 'WbsCode Forecast'[WbsCode.Discipline.display] ), [_Productive Labor Field (Forecasted Actual Cost)], IF ( HASONEFILTER ( Jobs[JobName] ), [_Productive Labor Field (Forecasted Actual Cost)], CALCULATE ( SUMX ( ALLSELECTED ( Jobs[JobName] ), [_Productive Labor Field (Forecasted Actual Cost)] ) ) ) ) Is there a way to calculate the avereage for the Adjusted Bid Factor column?Re: SUMX MAXX MINX dynamic X and Y Axis from multiple variables
v-kkf-msft This partically gets me there. I am wanting the MAXX value to be the same value between the two graphs. When I enter the MAX Y measure into the Y-axis conditional formatting range, it is not considering the max value between the two visuals.SUMX MAXX MINX dynamic X and Y Axis from multiple variables
I am needing to configure a dynamic x- and y-axis based on multiple slicer selections. The y-axis needs to be the max value between the two JobNames selected and the x-axis needs to be the min and max of the two JobNames selected. Its easy enough when only one selection is made, but I'm having difficulty when I add in the second selection variable. The sample data is linked here. Downloadable PBI File. _Dyanmic Axis Line Value = VAR SelectedJobs = ALLSELECTED(data2[JobName]) VAR SelectedDates = ALLSELECTED('data2'[TrxDate]) VAR LineValueMX = MAXX(SelectedDates,data2[Total Hours]) // works but is summing the values together for each date for example, on 03/31/31 MX has 33 people and CN has 32 people, it is returning 65 as the max when it should return 33 RETURN LineValueMX For the selections made, I'd expect the x-axis to start at 5/25/20 and to end at the max date, 4/15/22. The y-axis should register a max around 1500. Both graphs should abide by the same scale. Thank you in advance, and let me know if there is anything I can add.CONTAINSTRING help on row by row values
I'm trying to return a calculation that has is filtered by a numerator and denominator based on the ID selected out of the range of values. For each ID, I want to take the division of a selected few disciplines (e.g. VCD, QC, PM, PCN, PE) over another few (SMI, PFI, and PLI). Not every Id will have all attributes of the numerator but all Id's will have the same attributes of the denominator (SMI, PFI, and PLI). I had a hard time figuring out how to get a fixed denominator for a dynamic numerator, but now my numerator is also fixed based on the selections above. I need to to return those values per ID row in a table. So for the below snippet example, I'd expect the following result. DAX Sample is below. VAR _NumFilter = CALCULATE( SUMX( 'WbsCode Table', CONTAINSSTRING('WbsCode Table'[WbsCode.Discipline.name], {"VCD", "QC", "PM", "PCN", "PE" })), 'WbsCode Table'[ActualHrs]) VAR _numerator = CALCULATE(SUM ( 'WbsCode Table'[ActualHrs] ), _NumFilter ) VAR _Filter = FILTER ( ALLSELECTED ( 'WbsCode Table' ), 'WbsCode Table'[WbsCode.Discipline.name] IN { "PLI", "PFI", "SMI" } ) VAR _denominator = CALCULATE ( SUMX 'WbsCode Table'[ActualHrs]), _Filter )) VAR _Result = DIVIDE ( _numerator, _denominator, BLANK() ) RETURN _numerator
