Convert SAP BO measure with Foreach() operator to DAX formula
Hi I'm pretty new to SAP BO and I'm trying to convert a measure that I've found in a report to DAX language. The foruma uses the context operator FOREACH() on two columns ([Data].[KPI Group] and Data].[KPI Name] ) and I don't know how to write it in DAX. Could you please help me figuring out? Here's the code: SUM( IF( [Not Available] FOREACH( [Data].[KPI Group];[Data].[KPI Name] )="Y" ) THEN 1 ELSE 0 ) Thanks
Calculate values in one fact table from another fact table
Hi , I have following data model which has 2 fact tables 3 dimension tables and 1 bridge table to resolve many to many relationship. I am trying to calculate Sales Volume since the contract Start Date for each "Product ID" in Contracts Table using following formula Sales Volume Since Contract Start Date= CALCULATE ([Total Volume], FILTER ( SalesFact, SalesFact[INVOICE_DATE] >= MIN ( Contracts[Contract Start Date] ) ), SalesFact[PRODUCT_ID] IN VALUES ( Contracts[Product ID] ), SalesFact[BILL_TO_CUST_ID] IN VALUES ( Contracts[Bill To ID] ) ) I get correct results when I throw this measure in a table which provides Context for MIN ( Contracts[Contract Start Date] ) . However, if I throw this measure in a card visual or in any other visual without a Contract Start date context I get incorrect results. Can somone please tell me how can I resolve this? I think I need to use somekind of iterator function to get the result I need, but I can't seem to figure it out. Thanks!Cumulative Count with KPI
Hi, cumulative counts are covered extensively here but I can't see an answer to my specific problem. I'm trying to cumulatively count the number of calls a team of agents make in a day and benchmark them against a cumulative KPI. First I summarize the the number of agents per day based on the calls table. Active Agents = CALCULATE(COUNTROWS(SUMMARIZE(Calls,Calls[Date],Calls[AgentID])),FILTER(Calls,Calls[Role]="SalesAgent")) Then create the cumulative KPI for the calls up to yesterday and summed over "This Week" Calls KPI = CALCULATE([Active Agents]*60,ALL(Dates),Dates[This Week]="This Week",Dates[Date]<TODAY()) This works fine but when I do something similar for the number of calls they actually make during the week the KPI only shows me the previous day's total. Outbound Calls = CALCULATE(COUNTROWS(Calls),FILTER(Calls,Calls[Type]="O"),FILTER(ALLSELECTED(Dates),Dates[Date]<TODAY())) You can see in the images above when I inclide a date on the trend axis for the KPI the calls KPI is cumulative however the actual count of calls made is not. Removing the date gives the true cumulative figure for calls made but removes the trend axis. Can someone please help?
Filter and sclicer causing visualization discrepency
Hi I am a beginner in Power BI and I would like to know the feasibility/solution for my Queries. For a given data table "Prototype Modeling_freight" I have a measure as: volume = SUMX('Prototype Modeling_Freight', 'Prototype Modeling_Freight'[Sold Cases]) this converted to percentage for each type of equipment as: Volume distribution = DIVIDE([volume],CALCULATE([volume],ALL('Prototype Modeling_Freight'))),0) And visualized as clustered column chart Now there are specifically two things I want this chart to do: when I filter out through a date sclicer my chart must compare the data only within those sclicers, (note these sclicers can be other than dates) Eg: If you consider the Y axis in above picture, I want them to present 100%, or 'what was 25N's contribution in June month?'. Thank youCalculating Average of Sum Measurement
I have created a measurement to calculate the difference between two value from separate table based on date and then take the average of those values, but I can't seem to get the correct average value. The initial part of the measurement works correctly by getting the difference for each month, but I can't figure out how to get the overall per account_id average once I take out the Date Values in the context?? As you can see in the total line and last snapshot the value is incorrect the total average should be -.46 for the Account_id.
Is possible to ignore row filter context in a table ?
Hi! I have found various similar issues to this one in the forum but no one of solutions provided works for me 😞 I need for some calculations in a table, the value of the selected value in Group filter. It works OK in a card (as displayed in screenshot attached) , but, when I tried to use it in a table, it change between 2 and 3 (you can see in the table in the screenshot), I understand this is due to the row context filter is being applied to the measure. I need that the value "Selected Group" in my table remains being 3 (the one selected) not depending on row context filter, hoy can I reach that ? I tried with ALL, ALLSELECTED combinations with no success at all Currently my "Selected Group" Measure looks like: Selected Group = VAR currGroup = MAX('Table'[Group Code]) RETURN CALCULATE( MAX('Table'[Group Code]), FILTER ( ALLSELECTED('Table'), 'Table'[Group Code] = currGroup ) ) Thansk in advance!
