Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi
I have a dataset( see attached) .
The Table structure is
Stagename(Category) | Total | Date |
A | Values | A date (yyyy/mm/dd) |
B | Values | A date (yyyy/mm/dd) |
C | Values | A date (yyyy/mm/dd) |
D | Values | A date (yyyy/mm/dd) |
E | Values | A date (yyyy/mm/dd) |
Each stagename is unique to a day in a month and I want to display it in a report as
A | B | C | D | E |
100% | % of A | % of A | % of C | % of D |
I have connected the day of month to a dim_date table so ideally when the user selects a time frame this table changes,
I have experimented with the following formula but that calculates against all. How do i adjust the measure to calculate against a particular date only
%Against = divide(CALCULATE(sum([total]),'dim_date'[db_date]),CALCULATE(sum([total]),[stagename] = "Accounts Created"))
Any help will be greatly appreciated 🙂
Hi @fdkza,
I made one sample for your reference.
1. Create a calculated column.
rank = RANKX(ALL(Sheet1),Sheet1[stagename],,ASC,Dense)
2. Create a measure as below.
Measure = VAR maxrank = CALCULATE ( MAX ( Sheet1[rank] ) ) VAR SUMA = CALCULATE ( SUM ( Sheet1[total] ), FILTER ( ALLSELECTED ( Sheet1 ), Sheet1[rank] = maxrank - 1 ) ) RETURN IF ( MAX ( Sheet1[rank] ) = 1, 1, CALCULATE ( SUM ( Sheet1[total] ) ) / SUMA )
For more details, please check the pbix as attached.
Regards,
Frank
Thank you very much for this. I ideally want to display this using a date slicer at the top. How will i add the date to the measures ?
Thanks for all your help so far!
Hi @fdkza,
I cannot get you, Could you please share your excepted result to me?
Regards,
Frank
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |