cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

## Max value of certain dimension

Hello,
I have chart containing headcount numbers for certain period (headcount amount on value, date in axis for 13 months from slicer selection)
I want to get hightest value of headcount for department. Lets imagine that hightest value for headcount is 2000, and hightest in department is 1000 (rest 1000 are distributed amongs other departments). I'd like to get that value and convert it into %

Ideally I'd need two measures

1. Measure which retrives this value for the period of chart (so if i Select may 2022 I have axis from May 2021 till May 2022)

2. Measure which retrives this value for entire calendar table lenght

@SpartaBI
Could you help me on that? I hope i described it better here. Indeed, there is a need to get calculate on top of the measure you just came up with in my previous thread, but I don't know how to get filer context to let pbi evaluate it via department's headcount value

1 ACCEPTED SOLUTION
Community Champion

@Pbiuserr
Ok, let's split this to two, and start with question No 2:
if you add this as tooltip for your chart this will give you the answer for question 2:

``````Top Category Pct of Total =
VAR _table =
VALUES('Product'[Category]),
"@Qty", [Qty]
)
VAR _top_value =
MAXX(_table, [@Qty] )
VAR _total = [Qty]
VAR _result = _top_value / _total
RETURN
_result``````

 In case it answered your question, please accept it as a solution to help the other members find it more quickly. Appreciate Your Kudos 💪Showcase Report – Contoso By SpartaBI Website Linkedin Facebook This is SpartaBI!
3 REPLIES 3
Community Champion

@Pbiuserr hey, still not sure what you mean 🙂
Take a look at this screenshot (let's assume that my categories are your departments):

I added a matrix breakdown by cateogry for the same measure we did and we have there the max for each category for that period. Maybe we can go from there, what is the number you want to show and in which visual?

Post Prodigy

Hi,

Actually I'd like to have two measures (sorry but my business is like..they don't know theirselves what do they want thats why my description might not be flawless)

1) Its going to be 6300% from TV and Video as its hightest value out of all Category. Chart stays as it is. Is it for the duration of chart, or entire lenght of calendar?

2)Chart biggest value is January 2020 with 1907 qty. Out of those 1907, lets say 1000 is for TV and Games. I'd like to retrive that 1000 for games and convert it into percentage of grand total % from 1907

so 1000/1907

Community Champion

@Pbiuserr
Ok, let's split this to two, and start with question No 2:
if you add this as tooltip for your chart this will give you the answer for question 2:

``````Top Category Pct of Total =
VAR _table =
VALUES('Product'[Category]),
"@Qty", [Qty]
)
VAR _top_value =
MAXX(_table, [@Qty] )
VAR _total = [Qty]
VAR _result = _top_value / _total
RETURN
_result``````

 In case it answered your question, please accept it as a solution to help the other members find it more quickly. Appreciate Your Kudos 💪Showcase Report – Contoso By SpartaBI Website Linkedin Facebook This is SpartaBI!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.