User Profile
davebourgeois87
Frequent Visitor
Joined 4 years ago
User Widgets
Contributions
How to Calculate Share Measures using Share Bases Identified in a Reference Table?
Hi Everyone, I'm looking for guidance about how to incorporate a product reference table so that I can calculate multiple product share bases (i.e, category, segment, department, etc.). I have a normalized dataset that uses ProductID as the key between my data table and my product reference table and the relationship is set up correctly (simplifed example below). What I would like to be able to do is set up calculations like this: Dollar Share Segment = Var Numerator = [Dollars] Var Denominator = Calculate([Dollars],ProductID = SegmentID Return Numerator/Denominator Dollar Share Category = Var Numerator = [Dollars] Var Denominator = calculate([Dollars],ProductID = CategoryID Return Numerator/Denominator What I'm struggling with is the DAX function required to calculate the denominator. I'm not sure how to pull in the dollars for the required ProductIDs. I am not able to share an actual file due to data privacy guidelines that my company and my client have, but I'm just looking for any suggestions on which dax functions to use. Any help is greatly appreciated!Re: How to create measure using a specific column value as the denominator
Hi ribisht17 Here is a relevant data set: PROD DEMOS % of Total HH's DD Total Panel 84.20% DD Gen Z (18-29) with kids under 6 85.80% DD Millennial (30-39) with kids under 6 80.60% DD Gen X (40-54) with kids under 6 71.90% DD Brand 1 Total Panel 33.50% DD Brand 1 Gen Z (18-29) with kids under 6 37.00% DD Brand 1 Millennial (30-39) with kids under 6 33.00% DD Brand 1 Gen X (40-54) with kids under 6 26.00% DD Brand 2 Total Panel 28.90% DD Brand 2 Gen Z (18-29) with kids under 6 35.70% DD Brand 2 Millennial (30-39) with kids under 6 33.80% DD Brand 2 Gen X (40-54) with kids under 6 31.50% DD Brand 3 Total Panel 50.00% DD Brand 3 Gen Z (18-29) with kids under 6 54.60% DD Brand 3 Millennial (30-39) with kids under 6 47.70% DD Brand 3 Gen X (40-54) with kids under 6 39.20% My apologies, but I don't think I can share the actual file because it is on my organization's Sharepoint and is restricted. The data above is all that is needed though, I think. Essentially I want the calculation to happen separately for each product, but I want to divide all DEMOS except for Total Panel into DEMOS = Total Panel. So for PROD = DD, Gen Z/Total Panel, Millennial/Total Panel, Gen X/Total Panel, then the same for DD Brand 1. The result would look like this: I really appreciate your help! Let me know if my data table is enough.Re: How to create measure using a specific column value as the denominator
Hi ribisht17 Thank you for your quick response. I tried taking your suggestion, but it either does not work in this case or I did not apply the concept correctly. It ends up calculating within the demo instead of taking Gen Z % of HH's and dividing by Total Panel % of HH's: Do you see anything stand out that I did incorrectly? Thank you.How to create measure using a specific column value as the denominator
Hello, I'm still pretty new to Power BI and I'm having a ton of trouble understanding filter functions in DAX. Example: The above table is an example where I want to create a measure "% of HH's", which I have done successfully. However, I want to then index the values to the total line (Total Panel in example above). I tried creating a measure that returns the Total Panel value, but I can't use that as a denominator for the other values in the column because of filtering. What I want to happen is something like the below from Excel: Here is my current calculation: % HH Index = var denom = CALCULATE([Demo % of Total HH's],'Sheet1'[DEMOS]="Total Panel") return [Demo % of Total HH's]/denom*100 Any help is greatly appreciated!Re: Can you limit or filter the dataset data that loads into a Power BI Desktop Report?
Hi KNP , Thank you for the quick response. First, to address your question, the dataset was published by the team that manages the SQL server, and is essentially just a dump of the entire database. I do not have access to the SQL database itself, just the dataset that they published. I'm getting the data from the published data set: Not from the SQL server directly. When I use that option to load the data, I don't have the ability to transform the data. Between your message and the video that you recommended, it looks like I need to use the Get Data from SQL Server option to hook up to the database directly and then either implement a direct query to limit the data, or create aggregation tables using Power Query in the Transform Data stage. Does that sound correct? Sorry if this isn't making sense. I don't have a technical background. Just a business analyst trying to make the switch from Spotfire to PBI. Creating reports that link to an Excel report are no problem, but trying to link up to this SQL database has been a real challenge. I appreciate any help that you can provide.
