cancel
Showing results for
Did you mean:
New Member

## Format a dax query based on a slicer choice

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
6 REPLIES 6
New Member

I've managed to resolve this.

Thanks again

Julie

Solution Supplier

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é

Solution Supplier

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",

AVERAGE((sum(DataPBI[Numerator])),
DIVIDE(sum(DataPBI[Numerator]),sum(DataPBI[Denominator]))

)

``````

Kind regards,
José

New Member

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

Solution Supplier

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",

AVERAGE(DataPBI[Numerator]),
DIVIDE(sum(DataPBI[Numerator]),sum(DataPBI[Denominator]))

)
``````

I guess this should do

Kind regards,
José

New Member

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

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors