Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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".
Solved! Go to 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.
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,
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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |