Divide count by sum of the same column

[Sperling]

Hi,

I have a dataset similar to the information below, with customers paying different prices based on a chosen time slot in a calendar.

	Price	Amount of customers
	50	200
	75	100
	100	50
Total		350

Currently I use this measure to find the amount of customers:

Amount of customers =

CALCULATE(
COUNTROWS(QUERY),
QUERY[Calendar] = "/calendar"
)

I want to calculate the share of customers choosing each of the options.

I thought it was a simple task, but I can't seem to find out how to divide "Amount of customers" by the sum of "Amount of customers".

[Thejeswar]

HI @Sperling ,

You can get the % of customer using the below DAX formula

%ofCustomer = 
VAR currentamountofcustomers  = [Amount of customers]
VAR Totalcustomers = CALCULATE([Amount of customers], REMOVEFILTERS('Table2'))
RETURN
DIVIDE(currentamountofcustomers, Totalcustomers)

Mark the measure as a %

Below is the reference SS

Regards,

[Sperling]

Thanks for the quick reply!

The solution works when I have my other measures unfiltered.
However I'm looking at multiple sites, and when I filter the page to look at a specific site, it still uses the same total as the one from the variable you created below:

VAR Totalcustomers = CALCULATE([Amount of customers], REMOVEFILTERS('Table2'))

Any idea how to approach getting around this filtering issue?

[v-xiaotang · Accepted solution]

Hi @Sperling 

Try this, create the measures below,

SumEachPrice = 
    CALCULATE (
        SUM ( FactTable[Customer] ),
        FILTER (
            ALL ( FactTable ),
            FactTable[Price] = MIN ( FactTable[Price] )
                && FactTable[Date] >= MINX ( ALLSELECTED ( 'Calendar' ), Calendar[Date] )
                && FactTable[Date] <= MAXX ( ALLSELECTED ( 'Calendar' ), Calendar[Date] )
        )
    )

Sum of Amount_of_customers = 
    CALCULATE (
        SUM ( FactTable[Customer] ),
        FILTER (
            ALL ( FactTable ),
            FactTable[Date] >= MINX ( ALLSELECTED ( 'Calendar' ), Calendar[Date] )
                && FactTable[Date] <= MAXX ( ALLSELECTED ( 'Calendar' ), Calendar[Date] )
        )
    )

Amount of customers = 
    IF ( HASONEVALUE ( PriceTable[Price] ), [SumEachPrice], [Sum of Amount_of_customers] )

Divide = DIVIDE([Amount of customers],[Sum of Amount_of_customers])

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.