Sales through sets (assemblies)
Hello, I have a problem and I am not sure if I have my data model correct + need help with dax. I have few tables: 1) Pricelist with products - New pricelist: 2) Assemblies (which consist of the Products from table 1)) - TAssemblies 3) The Assembly configurations (here is information what amount of products is part of each assembly) - Assembly configurations 4) I create a dim Products and Assemblies table, where all items are listed (with additional information if it is a product or an assembly of products) - dim Products and assemblies 5) Sales table with information when, which product was sold in which amount - TSales I create a calendar table Calendar, and create a relationships between tables: I can easily calculate the price of an assembly: Right now I want to calculate the Turnover I made with my sales: To calculate the turnover I need to: If Type = Product get the price of each product and calculate it by amount sold\ else I need to calculate the price of the assembly by multiplying the price of each product * amount of product in this assembly * the amount of assemblies sold for which the Assembly no = Product or Assembly, (only for Type = Assembly) How should I do it? I mean I don't know how to write the DAX (it is probably easier than I suspect ;)) but also want to make sure my data model is correct and therefore I ask for Your help. I know I can modify the TSales table to remove the assemblies from there and input the products from the assembly but I would like to avoid it (during querying) - or it would be recommended I do this through edyting the query in such a way? Please find the demo file here: Calculating the sales through assemblies.pbixHow to add an additional filter on this DAX?
Hi community, This is very similar question to my previous post. I have my PBIX file uploaded here. I was trying to add a filter into this Measure called "Question" (shown below) wtih 'ProviderInfo'[Provider State] condition. But, the results did not come out as I wanted. The original Measure looks like this (without any filter for 'ProviderInfo'[Provider State]): If you look into the PBIX file, facilities ART & CAL belong to NM state, and facilities ALL & ARB belong to CO state. The separate Matrix visuals for NM and CO tabs use two separate data tables and not using any filter for state, so that the numbers dislaying are correct. ALL --> 18.67 ARB --> 30.67 ART --> 48.67 CAL --> 36.00 But, the Matrix in "Combined" tab is what I am trying to solve. If the measure "Question" is corrected, it should display as: ALL --> 18.67 ART --> 48.67 ARB --> 30.67 CAL --> 36.00 How do I modify the Measure "Question"? Thanks.Need some clarifications with TOPN & how to add more criteria
I have bottom DAX that illustrates TOPN: My understanding with TOPN is that it returns the top row. So, for this case, because we have same value for "MaxTotalWHSS", it would return 5 rows, instead of 1 row, correct? How do I add more criteria so that it would be something like: tblSurvey[MaxTotalWHSS] ASC and tblSurvey[ProcessingDate] ASC ? Thanks.Need help with DAX expression on RLS
I have attached PBIX file here. I am trying to modify the bottom DAX so that it meets the condition of AccessToAllLocations = 'Yes' as I intended. Bottom is current DAX that I have for RLS: VAR _Role = MAXX( FILTER( 'tblPBIGroup', 'tblPBIGroup'[User] = USERPRINCIPALNAME() ), 'tblPBIGroup'[AccessToAllLocations] ) RETURN IF ( _Role = "Yes", TRUE(), [User] = USERPRINCIPALNAME() ) I have two tables that are related to RLS: 1. 'tblPBISecurity' - this has all locations listed. 2. Second table is 'tblPBIGroup', which indicates that "AccessToAllLocations" is either 'Yes' or 'No'. The issue that I am having is when "AccessToAllLocations" is 'Yes', it should allow access to both locations 'ART' and 'CAL', but it only allows access to the location that is/are listed on tblPBIGroup (so in this case, it would only allow access to 'CAL' even though "AccessToAllLocations" is 'Yes'). Bottom is expectation of results (indicated on inside screenshot): How can it be achievable? Thanks for help.
Help with DAX
Dears All Pls, your help, I have a database in SQL called "table", and my problem is that I don't know how to have a measure that is not affected by the selection of 2 segments, which in my example are years. For this example I use the element called "Financial Reporting Matrix By Profitbase". I'm putting together a financial income statement that allows me to compare 2 selected years with a segmenter, as shown in the image: The database is similar to this in SQL, but with milions of rows: Which DAX function should I use or combine? Regards Rodrigo Puente
Semi-additive max date by account and category
Hi all, I am working with data which captures stages of clients from prospect to customer. each stage records a different potential contract value of the customer, and the latest date for particular customer holds the most accurate value I want to be able to extract by date/month etc. Link to dummy data in PBI for context: https://1drv.ms/u/s!AoIOEd5cDuqQuRmioxhdXlsHT3pX?e=72Nwue This is the sample data screenshot and desired output (count total max value of opp based on the last date the opp has been modified and so on for the count of opps based on stage and forecast category) I tried using Semi-Additive Measure to calculate sum for Last date of the day, but it only sums max opp_1 and max_date - It does not add multiple opp values to create pipeline of prospects&customers based on any given time period (month date) - see below Any help would be appreciated. 🙂
