I am now up to the final project for Power BI but I’m really stuck and don’t know why 🙁
I created the TotalSalesAmountALL to calculate the absolute total sales figure this like this:
TotalSalesAmountALL = CALCULATE([TotalSalesAmount],ALL(dim_Currency))
I have created the %sales measure with this formula:
%Sales = DIVIDE([TotalSalesAmount],[TotalSalesAmountALL])*100
The %Sales I create for each country all have 100%. I checked the lecture multiple times but the formulas are exactly the same.
What could be the problem?
Hi there 🙂
If you are completely sure that all three measures you need: TotalSalesAmount, TotalSalesAmountALL and %Sales are calculated n the same way as shown in the video, please take a look at your filter. You should be using dim_Currency -> CurrencyAlternateKey as your filter. I’d suggest you to delete the current filter you’re using and create a new one and then check if you have the right percentages showing. 😊 Hope this helps!
Hi Ned, thanks for the response. I checked the filter but the formula seems to correctly filter the CurrencyAlternateKey and the filter works correctly on all other things. An error that i discovered is that when I put the TotalSalesAmountALL in the last table of the project (the one with the sales amount for different dates and countries) it seems to give the same result as the TotalSalesAmount. I was expecting the ALL to only show the absolute total amount for each of the rows but it is giving total sales amount for each country. It seems to be kind of “filtered” by the countries. What should I do for more error checking?
I just got the answer using another way. I still don’t know why the previous way didn’t work but now I know how this other method works. I figured it out while I was trying to explain my problem to you lol. Thank you.
I have the same problem can i know how you solved it?
I had this same problem and solved it by doing TotalSalesAmountALL = CALCULATE([TotalSalesAmount],ALL(dim_Currency), ALL(dim_SalesTeritory))
(Note that the table has ‘territory’ misspelled, so be sure to reference the table correctly). The table is made from countries from the dim_SalesTeritory table, so the calculate aggregate function needs to ensure that it is not filtered by countries in the table.
Thank You Samantha 🙂 –> If anyone in the future runs into the same problem for the Sales Amount % displaying as 100% for all 6 countries –(which is weird because my Total Row was still 100% and not 600%)– Samantha’s alteration to “TotalSalesAmountALL” works! I do not understand why the dim_SalesTeritory table was not included in the “Total Sales Amount ALL” measure in the first place?