cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
23jools
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
23jools
New Member

I've managed to resolve this.

 

Thanks again

 

Julie

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
Solution Supplier
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",
    
    --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! 🙂

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

23jools_0-1659959053792.png

 

Thanks

 

Julie

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! 🙂

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

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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.