Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have the following data.
KPI | Actual | Country | Month |
KPI1 | 100 | A | 1 |
KPI1 | 200 | B | 1 |
KPI1 | 300 | C | 1 |
KPI1 | 400 | D | 1 |
KPI1 | 500 | E | 1 |
KPI1 | 50 | A | 2 |
KPI1 | 100 | B | 2 |
KPI1 | 150 | C | 2 |
KPI1 | 200 | D | 2 |
KPI1 | 250 | E | 2 |
KPI2 | 10 | A | 1 |
KPI2 | 11 | B | 1 |
KPI2 | 12 | C | 1 |
KPI2 | 13 | D | 1 |
KPI2 | 14 | E | 1 |
KPI2 | A | 2 | |
KPI2 | 11 | B | 2 |
KPI2 | 12 | C | 2 |
KPI2 | 13 | D | 2 |
KPI2 | 14 | E | 2 |
I have to get the KPI1/KPI2 vaue for Actual Country wise.
For Ex : for Country B : (200+100)/11+11 = 13.63 (Sum of Month 1&2 for Country B for KPI1/Sum of Month 1&2 for Country B for KPI2)
But If you see , KPI2, Month 2, Country A -> Does not have data. So while computing for Country A, since KPI2 i no havng data for Month 2, KPI1 for month2 Should also NOT be considered.
ex: For Country A : (100+blank)/(10+blank) =10
Inorder to calculate with this logic Iam planning to create a columm with comparing KPI1 and KPI2 values . Removng the 2n month value for KPI1 also
Actual_New |
100 |
200 |
300 |
400 |
500 |
100 |
150 |
200 |
250 |
10 |
11 |
12 |
13 |
14 |
11 |
12 |
13 |
14 |
Can someboy help on this?
Solved! Go to Solution.
@Anonymous,
Try these measures:
Total Actual = SUM ( KPITable[Actual] )
KPI Calc =
VAR vCountry =
MAX ( KPITable[Country] )
VAR vKPI2Table =
FILTER (
ALL ( KPITable ),
KPITable[Country] = vCountry
&& KPITable[KPI] = "KPI2"
&& NOT ISBLANK ( KPITable[Actual] )
)
VAR vKPI2Months =
SELECTCOLUMNS ( vKPI2Table, "Month", KPITable[Month] )
VAR vNumerator =
CALCULATE ( [Total Actual], KPITable[KPI] = "KPI1", vKPI2Months )
VAR vDenominator =
CALCULATE ( [Total Actual], KPITable[KPI] = "KPI2" )
VAR vResult =
DIVIDE ( vNumerator, vDenominator )
RETURN
vResult
Note: assuming the example of Country A should be (100+blank)/(10+blank) = 10
Proud to be a Super User!
Hi @Anonymous ,
According to my understand, if the Actual value is blank, then all Actual values of the same country and month are blank, right?
You could use the following formula after adding a CountryMonth column to get the new Actual values:
new Actual Measure =
VAR _BlankCountryMonth =
SUMMARIZE (
FILTER ( ALL ( 'Table' ), ISBLANK ( 'Table'[Actual] ) ),
'Table'[CountryMonth]
)
VAR _Actual =
SUMMARIZE (
FILTER ( ALL ( 'Table' ), 'Table'[CountryMonth] IN _BlankCountryMonth ),
'Table'[Actual]
)
RETURN
IF (
MAX ( 'Table'[Actual] ) IN _Actual
&& MAX ( 'Table'[CountryMonth] ) IN _BlankCountryMonth,
BLANK (),
MAX ( 'Table'[Actual] )
)
KPI1/KPI2 =
SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Country] = MAX ( 'Table'[Country] )
&& 'Table'[KPI] = "KPI1"
),
[new Actual Measure]
)
/ SUMX (
FILTER (
ALL ( 'Table' ),
'Table'[Country] = MAX ( 'Table'[Country] )
&& 'Table'[KPI] = "KPI2"
),
[new Actual Measure]
)
My final output looks like this:
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
Hi @Anonymous
1. Place Country in a visual table
2. Create this measure and place it in the visual:
Measure =
VAR auxT_ =
FILTER (
Table1,
VAR aux_ =
IF ( Table1[KPI] = "KPI1", "KPI2", "KPI1" )
RETURN
NOT ISBLANK (
CALCULATE (
DISTINCT ( Table1[Actual] ),
ALLEXCEPT ( Table1, Table1[Country], Table1[Month] ),
Table1[KPI] = aux_
)
)
&& NOT ISBLANK ( Table1[Actual] )
)
RETURN
CALCULATE (
DIVIDE (
CALCULATE ( SUM ( Table1[Actual] ), Table1[KPI] = "KPI1" ),
CALCULATE ( SUM ( Table1[Actual] ), Table1[KPI] = "KPI2" )
),
auxT_
)
3. looks like there's a mistake in your calculation or country A. Should be (100+blank)/(10+blank)
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@Anonymous,
Try these measures:
Total Actual = SUM ( KPITable[Actual] )
KPI Calc =
VAR vCountry =
MAX ( KPITable[Country] )
VAR vKPI2Table =
FILTER (
ALL ( KPITable ),
KPITable[Country] = vCountry
&& KPITable[KPI] = "KPI2"
&& NOT ISBLANK ( KPITable[Actual] )
)
VAR vKPI2Months =
SELECTCOLUMNS ( vKPI2Table, "Month", KPITable[Month] )
VAR vNumerator =
CALCULATE ( [Total Actual], KPITable[KPI] = "KPI1", vKPI2Months )
VAR vDenominator =
CALCULATE ( [Total Actual], KPITable[KPI] = "KPI2" )
VAR vResult =
DIVIDE ( vNumerator, vDenominator )
RETURN
vResult
Note: assuming the example of Country A should be (100+blank)/(10+blank) = 10
Proud to be a Super User!
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |