Format a dax query based on a slicer choice

[23jools]

Hi all

I'm quite new to PowerBi as well as this forum.  I wondered if someone could help me with a problem i'm having with my dax formatting.  I currently have a simple dax query calculating a divide of two fields in the same table.  My slicer is from my "IIForder" table which has 14 choices in field [IIF], 13 of these are % so i have easily formatted these using the format from the ribbon.  I now need to change just 1 of these options "ES-02" to average.   I've tried the iscrossfiltered solution but i don't think i've written this correctly.  

% IIF = DIVIDE(sum(DataPBI[Numerator]),sum(DataPBI[Denominator]))

Thanks in advance for any help regarding this query.

Julie

[23jools]

I've managed to resolve this.

Thanks again

Julie

[jcalheir]

Glad i could help,

if you have multiple scenarios you should look to use a Switch Function.

https://docs.microsoft.com/pt-pt/dax/switch-function-dax

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[jcalheir]

Hi,

You can use a IF function to test your slicer value, dont know you actual data, but you can try something like this

IF(
    SELECTEDVALUE('IIFolder'[choice]) = "ES-02",
    
    --Your average formula bellow
    AVERAGE((sum(DataPBI[Numerator])),
    DIVIDE(sum(DataPBI[Numerator]),sum(DataPBI[Denominator]))
    
   

)

 Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[23jools]

Thanks Jose

I'm getting an error message regarding the parameter not being the correct type.  Both my numerator and denominator are whole number formats

Thanks

Julie

[jcalheir]

It's normal, i misspeled the second argument. remove the SUM and just do the average from the Numerator.

IF(
    SELECTEDVALUE('IIFolder'[choice]) = "ES-02",
    
    --Your average formula bellow
    AVERAGE(DataPBI[Numerator]),
    DIVIDE(sum(DataPBI[Numerator]),sum(DataPBI[Denominator]))
    
   

)

I guess this should do

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[23jools]

Hi José

This has fixed my avg but now i need to get the other choices as a % and add the "Avg" after the average sum.  Sorry to be a pain, can this be done.  Below is my attempt to do this that has failed.

% IIF = IF(

SELECTEDVALUE('IIForder'[IIF]) = "ES-02",

AVERAGE(DataPBI[Numerator]),

divide(sum(DataPBI[Numerator]),sum(DataPBI[Denominator]))& " Avg"

if(

SELECTEDVALUE('IIForder'[IIF]) <> "ES-02",

divide(sum(DataPBI[Numerator]),sum(DataPBI[Denominator]))/100 & " %"))

Thanks again

Julie