Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a measure that calculates the percentage of column_numbers based on a selection which works on the total data when using specific boolean type filter based on column_boolean. The measure is as follows:
percentage = (DIVIDE(SUM(data[column_numbers]),CALCULATE(SUM(data[column_numbers]),ALL(data))))*100
However, I also have a column_dates in my dataset. When I select a specific month I want it to display the percentage of that specific month, and not the included records as a percentage of a whole.
Example of dataset:
column_dates | column_numbers | column_boolean |
Jan | 1 | true |
Jan | 2 | false |
Feb | 3 | true |
Feb | 4 | false |
Mar | 5 | true |
Mar | 0 | false |
Outcome examples:
selection 1: column_dates = all, column_boolean = true
expected outcome 1: 53% (calculation: 1 + 3 + 5 / 15)
selection 2: column_dates = Jan, column_boolean = true
expected outcome 2: 33% (1 / 3)
selection 3: column_dates = Jan & Mar, column_boolean = true
expected outcome 3: 75% (1 + 5 / 😎
With the current measure only selection 1 provides the correct result.
Solved! Go to Solution.
@Anonymous
pls try this
Measure = DIVIDE(sum('Table'[column_numbers]),CALCULATE(sum('Table'[column_numbers]),REMOVEFILTERS('Table'[column_boolean])))
pls see the attachment below.
Proud to be a Super User!
Thank you for your answer. Didn't know about the removefilters() function. Will need to do some research on it.
Thanks again.
you are welcome
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |