Dax function ALL automated on selected context?

DAX DAX Calculations
1

Question and example coming from studying the course Mastering DAX Calculations, which also explains the use of the function ALL

To use the ALL function within DAX you need to specify from which column the total is calculated, for example the column Customers.
For example:
Sales % of Total = divide [Total Sales],
CALCULATE( [Total Sales] , all(CustomerT[Customer Names] )))

With this function, you can then present the % sales per customer.
But as soon as the context in a visual changes, like channel or product, the results are meaningless (this formula then gives for every product or every channel 100%)

Is a more dynamic formula available, which calculate, dependent from the select context, the % of the total from the total ?
So that the formula always give a fraction of the total, based upon the selected context.
(Trying the measure Total Sales divided by ALL Total Sales gives an error.)

Thanks in advance for the response.

2

@deltaselect You can use ALL ( Fact Table ) to remove the filters from the whole model ( in reality expanded tables )

3

I would imagine that your model is a star schema. So you can just add the different dimensions to your ALL function, this way whatever dimension you use, it will give you the percentage.

Sales % of Total =
VAR Total_Sales = 
[Total Sales]
VAR All_Sales = 
CALCULATE( [Total Sales] , 
             ALL(CustomerT[Customer Names] ), 
             ALL(DimensionTable1(Column1), 
             ALL(DimensionTable2(Column2) ) 

RETURN 
DIVIDE ( Total_Sales , All_Sales)
4

Thanks for the instant respons !

Have tried Sales % of Total = divide( [Total Sales], all(Sales )) , which gives the following error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
Seems that adding the Facttable does not work.

5

@deltaselect Write it like this:

Sales % of Total =
DIVIDE (
    [Total Sales],
    CALCULATE (
        [Total Sales],
        ALL ( Sales )
    )
)
6

Thanks again !
That works fine, but I have a date slicer. The effect is that the slicer have to be selected for all dates, to get the 100% total.
How do you solve that ?

7

@deltaselect

Sales % of Total =
DIVIDE (
    [Total Sales],
    CALCULATE (
        [Total Sales],
        ALLEXCEPT ( Sales, Dates ),
        ALLSELECTED ( Dates )
    )
)
1 Like
8

This is a fast way of learning, thanks a lot !!

Your method works,
Why do you (still) need the last line in your code “ALLSELECTED ( Dates )” ?

Is it sufficient to code :
Sales33 % of Total =
DIVIDE ( [Total Sales], CALCULATE ( [Total Sales], ALLEXCEPT(Sales, DateT[Date]) ) )

9

@deltaselect You can remove that but if you have any column from the dates table in the visual you will need that.