Supplies are limited. Contact info@espc.tech right away to save your spot before the conference sells out.
Get your discountScore big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount
Hi ,
I have a table like this :
KPI ID | Value | Version |
1 | 10 | V1 |
1 | 9 | V1 |
1 | 5 | V2 |
2 | 3 | V1 |
2 | 4 | V1 |
3 | 7 | V1 |
4 | 6 | V1 |
4 | 6 | V2 |
5 | 15 | V1 |
5 | 12 | V1 |
5 | 100 | V2 |
I create a measure because data for KPI = 5 are not in the good aggregation.
Measure=
VAR _A =
CALCULATE (
SUM(Value),
KPI <>5
&& Version = "V1"
)
VAR _B =
DIVIDE (
CALCULATE (
SUM(Value),
KPI =4
&& Version = "V1"
),
CALCULATE (
SUM(Value),
KPI IN {1, 2, 3}
&& Version = "V1"
)
)
RETURN
KPI | Measure |
1 | 19 |
2 | 7 |
3 | 7 |
4 | 6 |
5 |
|
Desired result :
KPI | Measure |
1 | 19 |
2 | 7 |
3 | 7 |
4 | 6 |
5 | 0.18 |
Can you help me?
@Ybam When KPI is 5, the other KPI numbers are not in context so you need to use ALL or ALLSELECTED to bring them back into context like this:
Measure=
VAR _A =
CALCULATE (
SUM(Value),
KPI <>5
&& Version = "V1"
)
VAR _B =
DIVIDE (
CALCULATE (
SUM(Value),
ALL('Table'),
KPI =4 && Version = "V1"
),
CALCULATE (
SUM(Value),
ALL('Table'),
KPI IN {1, 2, 3} && Version = "V1"
)
)
RETURN
IF (
Max(KPI)= 5,
_B
, _A
)
@Greg_Deckler Thank you very very mutch it works.
But if I want to slipt result my month I have only total .
I would like my result loolks like this.
Month | Jan. | Feb | March | .... |
KPI | Measure | Measure | Measure | |
1 | 19 | 5 | 5 | |
2 | 7 | 10 | 5 | |
3 | 7 | 7 | 8 | |
4 | 6 | 9 | 3 | |
5 | 0.18 | 0.41 | 0.17 |
User | Count |
---|---|
12 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
19 | |
14 | |
10 | |
7 |