10-01-2020 07:21 AM
Firstly thanks Greg for this great article.
when dealing with a measure that is in itself a sumx , is there something i need to do differently in creating the variable table?
the formula works for each month but total is returnign zero and i ssupect it is due to the sumx formula not creating the summarizes table correctly.
After looking at this a bit have developed an alterative solution:
i created an index column in the data table for the level of calculations i need ie Customer + Product+ Month
Then i created a new table summarising that column and linked the new table back to the data table.
Then in the sumx formula used the new table as the reference Sumx( new table (custProdMonthnum), calculations)
this means it forces the total column to do the calcs at the month level, so all adds up. also it means just one formula and doesnt worry if you add in qrtly totals etc..
I suggest you post your question as a new thread in the "Help" Forum. I don't think this thread should be used to ask questions for Help. You will get many more eyes looking at it which will assist you further.
Also please include as much detail as you can.
Good Luck and Best Regards,
@rj10014 Don't feel bad man, this one comes up a LOT!! It is super unintuitive to people just learning DAX but is a great example to help expand your understanding of how important context is to DAX!
Below is a screen shot from my excel file that I have uploaded into Power BI.
I want to show that in Cook County (for my current period and for the fuel type water) that Brand 1 has sold a total of 8, which is equal to 13% share
In Power BI if I filter on Cook I get what I need.
When I add another County, the numbers get messed up.
Where am I going wrong. I’ve attached the pbix file. I am not a Dax guy, I am trying to learn. Thanks.
I have a calculated field Goal which is not giving the sum correct.
How do I make it working?