Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
troyel
Advocate II
Advocate II

Show max and min from a filtered list as KPI

Hi,

I have a data set where I have numerator and denominators in different columns (and rows) for different projects. 

 

I use a measure to calculate the resulting percentage of these figures. ((SUM(Table[Numerator])/SUM(Table[Denominator])))

 

Currently when I view these results they are just the sum of all projects numerator divided by the sum of all projects denominator. What I want to get are the max, min and average of these - for each project as a KPI/single figure. How can I do this? See example data set in excel.

 

Thanks!

 

Results I want in the measure is highligted in yellow:
Example

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @troyel,

 

You can try to use below formulas if it suitable for your requirement.

 

Summary table and calculate the percent:

Summary =
ADDCOLUMNS (
    SUMMARIZE (
        Table1,
        [Project],
        "Value", SUM ( Table1[Value] ),
        "Denominator", SUM ( Table1[Denominator] )
    ),
    "Percent", [Value] / [Denominator]
)

2.PNG

 

Measures:

AVG=AVERAGE(Summary[Percent])
MAX=MAX(Summary[Percent])
MIN=MIN(Summary[Percent])

3.PNG

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

HI @troyel,

 

You can try to use below formulas if it suitable for your requirement.

 

Summary table and calculate the percent:

Summary =
ADDCOLUMNS (
    SUMMARIZE (
        Table1,
        [Project],
        "Value", SUM ( Table1[Value] ),
        "Denominator", SUM ( Table1[Denominator] )
    ),
    "Percent", [Value] / [Denominator]
)

2.PNG

 

Measures:

AVG=AVERAGE(Summary[Percent])
MAX=MAX(Summary[Percent])
MIN=MIN(Summary[Percent])

3.PNG

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Ah -  I had not considered creating a separate table to do this. Smart - thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.