Hide categoris values if measures value less then 3
Hi, im new in power bi and i need to produce this result.... I have any measure aggregations that in certain scenarios ups to value of 3 (deceases, patients, fragile subjects and go on....) in that case i have not to show that but that if U see, the real category values 60-69 and 70-79 are replaced with an anoymous "N.I." (means Not Identified) And this is just a great challenge form me BUT i have another difficult problem to solve, because in my pbi report i have to activate fields parameter to offer a multi selection feature to user (in categories and also in measures). Because they have to be free to choose what to aggregate in that visual... can anyone helps me ? thanks at all folks! best regards Mauro
Calculated table - 10 absence occurred within 6 weeks
Any help would be appreciated. How would I create a column that checks against ID and provides yes for 10 absence occurrences within 6 weeks. When it hits 10 occurrences it provides yes. If the absence carries on it does not count unless it is 10 more within 6 week.
Percentile of a table stored in a variable
Hi all, I would like to detect outliers in my daily data report by using the percentile function. I have a detail table (table 1) and I want to calculate the percentile of the second table which shows the tickets per day. If I create a fixed calculated table I have to add "ALL / AllExcept" function inside Percentile function: Percentile = PERCENTILEX.EXC (ALL(TicketsByDate), TicketsByDate[Count_Tickets], .90 ) I need to apply external filters and I don't want to rely on the All/AllExcept function. For this reason, I want to store my calculated table in a variable. The problem is that I can't calculate the percentile of this calculated table in a variable. I get the following error: "If k is not a multiple of 1/(n + 1), PERCENTILEX.EXC will interpolate to determine the value at the k-th percentile." Can someone help me? Thanks!!!
Calculated Groups complicated DAX
Hello folks, I have a complicated dax that works perfectly on its own when I visualize it in PBI (it consists of 3 measures). But I need to show this measure in different time comparisons so this is why I copied the measures to tabular editor and put together the three measures into one calculation item. There is no bug on the calculation item page, it is saved, but PBI matrix visual shows incorrect 0 values. Here are the measures separately in PBI (original calculations): Valuerunningtotalbysprintwk = CALCULATE ( [Velocity], FILTER ( ALL ( 'Calendar' ), 'Calendar'[Sprint Day CC] <= MAX ( 'Calendar'[Sprint Day CC] ) && 'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] ) && 'Calendar'[Sprint Wk CC] = MAX ( 'Calendar'[Sprint Wk CC] ) ) ) ValuerunningtotalpreviousS = VAR previousyearlastsprintwknumber = MAXX ( FILTER ( ALL ( 'Calendar' ), 'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] ) - 1 ), 'Calendar'[Sprint Wk CC] ) RETURN IF ( SELECTEDVALUE ( 'Calendar'[Sprint Wk CC] ) <> 1, CALCULATE ( Valuerunningtotalbysprintwk, FILTER ( ALL ( 'Calendar' ), 'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] ) && 'Calendar'[Sprint Wk CC] = MAX ( 'Calendar'[Sprint Wk CC] ) - 1 && 'Calendar'[Sprint Day CC] = MAX ( 'Calendar'[Sprint Day CC] ) ) ), CALCULATE ( Valuerunningtotalbysprintwk, FILTER ( ALL ( 'Calendar' ), 'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] ) - 1 && 'Calendar'[Sprint Wk CC] = previousyearlastsprintwknumber && 'Calendar'[Sprint Day CC] = MAX ( 'Calendar'[Sprint Day CC] ) ) ) ) MainKPI= DIVIDE( Valuerunningtotalbysprintwk - ValuerunningtotalpreviousS, ValuerunningtotalpreviousS ) I have created variants to be able to put them together to the calculated items page (tabular editor): VAR Valuerunningtotalbysprintwk = CALCULATE ( SELECTEDMEASURE(), FILTER ( ALL ( 'Calendar' ), 'Calendar'[Sprint Day CC] <= MAX ( 'Calendar'[Sprint Day CC] ) && 'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] ) && 'Calendar'[Sprint Wk CC] = MAX ( 'Calendar'[Sprint Wk CC] ) ) ) VAR ValuerunningtotalpreviousS = VAR previousyearlastsprintwknumber = MAXX ( FILTER ( ALL ( 'Calendar' ), 'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] ) - 1 ), 'Calendar'[Sprint Wk CC] ) RETURN IF ( SELECTEDVALUE ( 'Calendar'[Sprint Wk CC] ) <> 1, CALCULATE ( Valuerunningtotalbysprintwk, FILTER ( ALL ( 'Calendar' ), 'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] ) && 'Calendar'[Sprint Wk CC] = MAX ( 'Calendar'[Sprint Wk CC] ) - 1 && 'Calendar'[Sprint Day CC] = MAX ( 'Calendar'[Sprint Day CC] ) ) ), CALCULATE ( Valuerunningtotalbysprintwk, FILTER ( ALL ( 'Calendar' ), 'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] ) - 1 && 'Calendar'[Sprint Wk CC] = previousyearlastsprintwknumber && 'Calendar'[Sprint Day CC] = MAX ( 'Calendar'[Sprint Day CC] ) ) ) ) VAR RESULT = DIVIDE( Valuerunningtotalbysprintwk - ValuerunningtotalpreviousS, ValuerunningtotalpreviousS ) RETURN RESULT The main purpose of this calculation is to see how the selected measure improved compared to the previous sprint, but also thinking about which day the report is open, because I want compare the actual sprint value on the latest day compared to the previous sprint with the same day running total. If I only copy this part of the measure to tabular editor, the value shows up in PBI: Valuerunningtotalbysprintwk = CALCULATE ( SELECTEDMEASURE(), FILTER ( ALL ( 'Calendar' ), 'Calendar'[Sprint Day CC] <= MAX ( 'Calendar'[Sprint Day CC] ) && 'Calendar'[Sprint Year CC] = MAX ( 'Calendar'[Sprint Year CC] ) && 'Calendar'[Sprint Wk CC] = MAX ( 'Calendar'[Sprint Wk CC] ) ) ) Any help is appreciated, how I could fix the formula in editor to see the values in PBI. Many thanks in advance!Calculate daily budget from monthly budget
Hello, I have a budget that is monthly and I want to convert that into daily values, simple in SQL but I'm new to DAX. The ContractBudget table is imported from SQL The DateDax is a date table generated by DAX. I currently have no relationship between the tables. In SQL I'd join ContractBudget.Date=DateDax.FirstDayOfMonth to get a budget value for every day of the month but PowerBI says that's a many to many, and there be grumpy dragons ahead. How do I get a budget value for each day where I divide DecimalBudget by Days In Month to arrive at my daily value? I'd do a merge in Power Query but calculated tables don't show up there. Is that correct?How to add Calculated rows in a PBI Matrix
Hi all, I want to insert calculated rows to a power BI matrix. How do i add this? I can add calculated columns. But not sure how to structure the data and what are the functions to use to do this; 2018 Vol(KG) 100 Turnover 12,000 Cost (8,000) Gross Profit 4,000 Cost/KG 40 Gross margin 33.3% This is what the matrix should look like. Cost/KG and Gross margin are calculated using above rows. Please assist
