top and flop measure with switch between % and €

[RH205]

hi all,

I have been struggeling to get this right. example:

I want to show the top 5 and flop5 projects (name + number in projecttable) and based on the measure 'return' --> revenue (invoice table) minus cost (projectcosts table).

I rather stay away from visual filters, and I want to switch between return in euro and percentage based on a slicer. What are the options? I tried dax, calculatedtable, field parameters but nothing worked. Either the  amount was off, or it showed more than 5.. i am lost..anybody?

[V-yubandi-msft]

hello @RH205 ,

Please let me know if my response clarified things for you. If you need any further explanation or additional details, feel free to ask.

[V-yubandi-msft]

Hello @RH205 ,

If there's a chance to have your response officially reviewed, that would be ideal. Otherwise, I’ve already reviewed it to ensure clarity and completeness.

Thanks&regards,

Yugandhar.

[V-yubandi-msft · Accepted solution]

Hi @RH205 ,

Thank you for reaching out to the Fabric Community. I’ve reproduced your scenario using some dummy data and was able to achieve the expected outcome

FYI:

Please take a look and let me know if you need any adjustments. I’ll be happy to assist further.

Thanks & regards,
Yugandhar

[RH205]

thanxs!

[grazitti_sapna]

Hi @RH205,

Follow below fix, created DAX

Create a table for the slicer

ReturnType = DATATABLE(
"Metric", STRING,
{
{ "Return (€)" },
{ "Return (%)" }
}
)

Create the Dynamic Return Measure

Return_Metric :=
VAR Revenue = CALCULATE(SUM(Invoice[Amount]))
VAR Cost = CALCULATE(SUM(ProjectCosts[Cost]))
VAR SelectedMetric = SELECTEDVALUE(ReturnType[Metric], "Return (€)")

RETURN
SWITCH(
SelectedMetric,
"Return (€)", Revenue - Cost,
"Return (%)", DIVIDE(Revenue - Cost, Cost),
BLANK()
)

Create Top 5 and Flop 5 Tables Using RANKX

TopFlopProjects =
VAR BaseTable =
ADDCOLUMNS (
VALUES ( Projects[ProjectID] ),
"Return", [Return_Metric]
)

VAR Top5 =
TOPN ( 5, BaseTable, [Return], DESC )

VAR Bottom5 =
TOPN ( 5, BaseTable, [Return], ASC )

RETURN
UNION ( Top5, Bottom5 )

Create a Relationship (or Use LOOKUPVALUE)

IsTopOrFlopProject =
IF (
Projects[ProjectID] IN VALUES(TopFlopProjects[ProjectID]),
1,
0
)

And filter visuals where IsTopOrFlopProject = 1.

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[FBergamaschi]

This is a DAX question, please delete and repost here

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/bd-p/DAXCommands

Thanks 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[FBergamaschi]

Meanwhile, the answer is here (shall repost in the DAX section what is below )

Top 3 Project =

VAR Top3Prod =

TOPN (

    3,

    ALL ( Projects[Project Nr] ),

    [Revenues]-[Costs]

)

RETURN

CALCULATE( [Revenues]-[Costs], KEEPFILTERS( Top3Prod ) )

Top 3 Project Pct =

VAR Top3Prod =

TOPN (

    3,

    ALL ( Projects[Project Nr] ),

    [Revenues]-[Costs]

)

RETURN

CALCULATE( DIVIDE ( [Revenues]-[Costs], [Revenues]), KEEPFILTERS( Top3Prod ) )

Top 3 Project Pct assumes yu still wanto to identify the Top based on [Revenues]-[Costs] but want to calculate the margin of those projects as a pct of sales (DIVIDE ( [Revenues]-[Costs], [Revenues]) )

If this is not what you want pls be more specific on this

With a field parameter you can then choose which measure to use

Please do not forget to cancel and report in the righe section

If this helped, please consider giving kudos and mark as a solution

@mein replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI