metric

4 Topics

Filter context not passing onto measure
Hi Power BI Brains! I need a bit of help please, I've been stuck on this problem and don't fully understand why it's happening. I've got a dataset that contains a Calendar table and a Sales Detail fact table that have a one to many join between them, single direction from Calendar to the Sales table. I've created a table of explicit measures: a set of measures for the current year and a set for the previous year (based on the dates in my Calendar table where each calendar date has a YOY date in the financial year which does not align with the standard gregorian calendar). Sales Unit Va lue= sum('Sales Daily Detail'[Invoiced Sales Unit Value]) Sales Unit Value LY = var _MaxDate = CALCULATE(MAX('Sales Daily Detail'[YOY Event Date])) var _MinDate = CALCULATE(MIN('Sales Daily Detail'[YOY Event Date])) RETURN CALCULATE([Sales Unit Value],ALL('Sales Daily Detail'),'Sales Daily Detail'[Event Date]>=_MinDate&&'Sales Daily Detail'[Event Date]<=_MaxDate) The LY measure works as intended, it's supposed to be flexible so that when the user uses the dataset for self-serve queries they can use the LY metric for whatever date range they need. If I filter the Calendar table with a slicer, I get the correct LY value, happy with that part. But... this is where the problem comes, I then I created a DateSelections table as per this video (https://www.youtube.com/watch?v=fKygF7VEJnQ) to provide my users with a pre-defined list of time periods they can select in the report as well as a Custom Date range, because this dataset is also used for a lot of dashboards. The DateSelections table that I created for my pre-defined periods has a one to many relationship filtering both ways to the Calendar table and has *no* relationship to the fact table. If I select let's say "Week to Date" on my dropdown using DateSelections then it goes and filters my Calendar table which then correctly filters my Sales Fact table and returns the correct Sales Unit Value. Here's the weird part... Sales Unit Value LY goes blank! If I create a slicer that has any field in from the Calendar table and I use that to filter the sales fact talbe, Sales Unit Value LY is correctly returned. If DateSelections filters Calendar and Calendar filters Sales fact then why is the Sales Unit Value LY metric not populating at all when I make a selection in DateSelections? I am quite confused, I suspect it's to do with not being able to pass a filter context onto a second table or something? I tried adding an inactive relationship between DateSelections and the Sales Fact and then use USERELATIONSHIP but the problem persisted. Any thoughts on what I'm doing wrong??
Solved
Anonymous
3 years ago Place DAX Commands and Tips
9.5KViews
0likes
8Comments
Create metric with same value based on specific row
Dear PBI community, In the picture below, I have a column with sum of total sales for all countries. I would like to add a NEW metric (for example Total Sales of USA) which will have value of United States sales on all rows. Consequently, I would create ANOTHER NEW metric and divide (result will be in %) column Total Sales of USA by column Total Sales. How am I able to achieve it with DAX? Thanks a lot for your help, I have gone through some forum threads but I could not find answer for this specific challenge.
Solved
ElistheFox
3 years ago Place DAX Commands and Tips
880Views
0likes
3Comments
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!!!
Solved
Anonymous
4 years ago Place DAX Commands and Tips
3.6KViews
0likes
6Comments
[HELP] Complex Metric Formula
Hi! I need your help with a DAX metric. I have an table where there are FORMULA nodes (red arrow in the image) composed by other "simple" nodes or from these own formulated nodes. Each of these formulated nodes can have additions, subtractions, multiplications or divisions (+ , - , *, / ) with other nodes. I have a metric created (IMPORTO_ACUMULA in the image) that calculates the data for the simple nodes. How can I create a unique complex metric that evaluates the formula and calculates the IMPORTS for each node ? THANKS IN ADVANCE!!!!!!!!
PMPalex
7 years ago Place DAX Commands and Tips
804Views
0likes
0Comments