Using Calculate vs TotalSalesAmount
Can you go into more detail as to why we shouldn't just divide the SalesAmountUnitedStates by TotalSalesAmount? They give the same result, and I'm having trouble understanding your explanation regarding BI dividing line by line as opposed to the entire total from all countries.
Hi Kevin,
If we forget about the US Sales Amount and focus on getting the percentage of Sales Amount by Country, how would you go about it? For example, let's say we want to find out Total Sales Amount for each country, so we build a chart that holds all countries, and write a measure that divides the sum of sales amount by the sum of sales amount. This result will return 100 for each country, because what DAX will do it will go to line United States - it will recognize that the sales amount for US is 9 million but because there is no ALL function to specify that the denominator should be the total sales amount of all countries it will divide 9 million by 9 million and it will return 100%, the same will happen for the rest of the countries. which will be incorrect and will require the ALL function.
If we now go back to the US Sales Amount that is shown in a card, the following is happening inside the background. First we have a filter modifier in the nominator that says give me the total sales amount for the United States, this is the base value, then we divide it by the sum of total sales, since there is no row context in the card visual here the DAX engine returns the full sales amount and it gives you the correct result. However, if we put this value inside a table and put together the rest of the countries inside the table, you will notice that the US sales amount will be equal to 100% and the rest of the countries will produce incorrect results. I hope this helps.
Kind Regards,
Dimitar Shutev