Excel in office 365
I have an existing dashboard where I calculate the results for certain metrics, and the user can use a slicer to filter data after that. I have been asked to add additional information that will fundamentally change the way the calculations work.
How the data looks currently:
ID | Metric Name | Metric Value | Status | Type | Manager |
1 | A | 1 | Open | Type1 | Manager 1 |
1 | B | 5 | Open | Type1 | Manager 1 |
1 | C | 20 | Open | Type1 | Manager 1 |
2 | A | 1 | Closed | Type2 | Manager 2 |
2 | B | 8 | Closed | Type2 | Manager 2 |
2 | C | 26 | Closed | Type2 | Manager 2 |
3 | A | 1 | Open | Type1 | Manager 3 |
3 | B | 6 | Open | Type1 | Manager 3 |
3 | C | 19 | Open | Type1 | Manager 3 |
4 | A | 1 | Open | Type3 | Manager 1 |
4 | B | 3 | Open | Type3 | Manager 1 |
4 | C | 34 | Open | Type3 | Manager 1 |
The Dax calculation I am using in a pivot table:
=CALCULATE('Table1[Sum of Metric Value]/'Table1' [Count of Metric Value],'Table1'[Type]="Type2",'Table1' [Metric Name]="B", 'Table1' [Status]="Open")
This gives the accurate result of metric value for the specified metrics, filtering for the other information that’s relevant to the metric.
Users are able to filter based on a slicer for manager name attached to the pivot table.
_______________________________________________________________________________________________
Now this is how the data looks. There is one, or more, rows with the same metric name, with a duplicate of metric value, but a different worker row.
ID | Metric Name | Metric Value | Status | Type | Manager | Worker |
1 | A | 1 | Open | Type1 | Manager 1 | Adam |
1 | B | 5 | Open | Type1 | Manager 1 | Fred |
1 | B | 5 | Open | Type1 | Manager 1 | Susan |
1 | C | 20 | Open | Type1 | Manager 1 | Fettuccini |
1 | C | 20 | Open | Type1 | Manager 1 | Alfredo |
2 | A | 1 | Closed | Type2 | Manager 2 | Adam |
2 | B | 8 | Closed | Type2 | Manager 2 | Karen |
2 | B | 8 | Closed | Type2 | Manager 2 | Adam |
2 | C | 26 | Closed | Type2 | Manager 2 | Susan |
2 | C | 26 | Closed | Type2 | Manager 2 | Amy |
3 | A | 1 | Open | Type1 | Manager 3 | Adam |
3 | B | 6 | Open | Type1 | Manager 3 | Shirly |
3 | B | 6 | Open | Type1 | Manager 3 | Annie |
3 | C | 19 | Open | Type1 | Manager 3 | Amy |
3 | C | 19 | Open | Type1 | Manager 3 | Carol |
4 | A | 1 | Open | Type3 | Manager 1 | Adam |
4 | B | 3 | Open | Type3 | Manager 1 | Fred |
4 | B | 3 | Open | Type3 | Manager 1 | Adam |
4 | C | 34 | Open | Type3 | Manager 1 | Fettuccini |
4 | C | 34 | Open | Type3 | Manager 1 | Alfredo |
This was my idea for a formula, but apparently Excel Dax doesn’t have the FIRSTNONBLANK function in it, so I couldn’t test it out.
=CALCULATE(
SUMX(
DISTINCT('Table1' [Metric Name]), FIRSTNONBLANKVALUE('Table1' [Metric Value],0))
/'Table1' [Count of Metric Value],
'Table1' [Type]="Type2",'Table1' [Metric Name]="B", 'Table1' [Status]="Open")
Are there any other solutions that would give me the correct result without duplicating the metric value when no slicer filter is applied?
