Forum Discussion
Table Creation Challenges with formulas
I am brand new to Power BI formulas and having a hard time understanding proper language. With that said, I'm trying to get a GM (Gross Margin calculation from 2 separate data sets. I have a total revenue calculation and also a total cost. I want it to update as slicers are utilized on the dashboard. So, I don't want to just have one number that is aggregated. I'm trying to sum the columns in each data set and then do a fomula from that to calculate GM%. I was trying to use MS Excel knowledge and do the below but it just doesn't work. Is anyone able to show someone who has absolutely no idea what they are doing a way to get this formatted properly?
5 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523I have two separate tables that I want to look at and calculate GM% based on contracts being added and cost/revenue adjusting. In excel, this would simply be =(Revenue!E2-Cost!E2)/Revenue!E2. When I'm trying to insert a new table within DAX, I can't seem to get the formula correct. I'm trying to create a Gauge visual where min is 0, Max is 100% and actuals being the GM% as calculated by Cost and Revenue tables. I'm sure there is something very simple I am missing but, as stated above, I'm really new and have no idea how to format that code/formula. Below is a sample of how I have it formatted in the source file.
Status Customer Contract Region Total Revenue Active 1 Flower West $ 1,000,000.00 Inactive 2 Tree East $ 500,000.00 Active 3 Grass Central $ 750,000.00 Active 4 Car South $ 1,250,000.00 Status Customer Contract Region Total Cost Active 1 Flower West $ 750,000.00 Inactive 2 Tree East $ 400,000.00 Active 3 Grass Central $ 600,000.00 Active 4 Car South $ 950,000.00 What's the dimension tying these two tables together? Is the Customer column your primary key?
Status is the initial dimension used from a slicer perspective. It is essentially all 4 dimensions(status, customer, contract, region). I'm utilizing slicers to toggle between all of those dimensions on our dashboard to show performance KPI's by project status, customer, contract, region. I have a separate table showing GM%. What's happening is that when I have "ALL" selected in my slicers, is that it is giving me an average of an average, and not a true Gross Margin of all projects. In this example, my gauge would show 22.25%. When in reality if you take total Revenue of $3,500,000.00 and a total Cost of $2,700,000.00, it is 22.85%. That's a delta of more than 1/2 a % on Margin.
Status Customer Contract Region GM% Active 1 Flower West 25% Inactive 2 Tree East 20% Active 3 Grass Central 20% Active 4 Car South 24% You need a data model, and have your dimensions control both fact tables.
