Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
SachinC
Helper V
Helper V

Percentage DAX Formula

Hi,

I want to create a percentage figure, i.e. x%, from two text fields.

It's basically calculating click through rate as a %. I've got a count of my audience for a mailshot in a column, a count of clicks in another column but I need to compute click through % line by line.  Help!? 

Regards,

S

25 REPLIES 25
SerenatMacar
New Member

Hi! Try to create a measure like this and use FORMAT to get the percentage value.

BluePercentage =
var __bluePerc = DIVIDE([BlueNumber], [BlueNumber]+[GreenNumber])
return "The"&FORMAT(__bluePerc, "Percent") &" of the values are blue."
ovetteabejuela
Impactful Individual
Impactful Individual

Sample data would help, not sure what you're trying to do but have you tried DIVIDE and format the resulting column to a Percentage?

New Column Name = Divide(Column A,Column B)

 

And then format New Column Name to percentage.

 

Reply to User.PNG

Hi,

 

I tried to use Divide with Calculated column and got Blank value. Not sure why.

Not sure if it's late in the game but when you're trying the suggestions are you doing it on a separate pbix environment and not in your main data model?

 

I asked because usually when we ask for help we only take a portion of the data model and present to the community. when they do the solution it woluld work but when we apply it to the main data model it would...

 

Then there might be something from the data model that is causing this...

Hi thanks for that. However, my values for Column A and Column B is text.  I am counting Column A and ColumnB but I want to do an inline percentage. See example below.  33 and 31 are counts of Text fields, I want to put an inline column to divide 31/33= 93.93%

Thanks.

 

zzzz.PNG

Would you be able to post a dummy data keeping the table structure?  and also if possible the result desired.

See attached. I have manually computed the % value in the pivot, I can't seem to do this in PowerBI. 

All the columns are text, I am doing counts to get numeric values from them.  Thanks.zzzz1.PNG

 

zzzz2.PNG

Vvelarde
Community Champion
Community Champion

@SachinC

 

Hi, please try with these measures:

 

CountAudiences = COUNTA(Table1[Audience])

CountClicks = CALCULATE(COUNTA(Table1[Clicks]),FILTER(Table1,Table1[Clicks]<>BLANK()))

% = DIVIDE([CountClicks],[CountAudiences])




Lima - Peru

Are you sure the CountClicks formula is correct? The FILTER bit doesn't seem to work.  Please help?  Thank you.

Vvelarde
Community Champion
Community Champion

@SachinC

 

Please show me the result of CountClicks..

 

it gives you an error or the result is not the correct?




Lima - Peru

Hi,

See attached. It's as if it's putting extra lines in for the same values and making % = 1.00.

Pls help.

zzzzCapture.PNG

Thanks

.

Vvelarde
Community Champion
Community Champion

@SachinC

 

in my test file the CountClicks works Ok.

 

Let me see:

 

-Your Click column is Text.

-When No have clicks is a blank row in this column? or have a space or something.  To discard this add a calculated column and put 

 

Column=Len(ColumnwithClicks) the result to empty rows should be 0.

 




Lima - Peru

I think I understand where you are coming from but I am lost.  Can you explain again please.  you are right, the values have blanks. Thanks.

Vvelarde
Community Champion
Community Champion

@SachinC

 

I just need to discard that are empty values.

 

Because the CountClicks Dax filter the blanks so if you have 10 rows and 3 are empty the result should be 7. If the result is 10 then not are empty values.

 




Lima - Peru

My CountClicks is this:  CountClicks = CALCULATE(COUNTA([Clicks]),FILTER(VPBI_Marketing_MailerStats,[clicks]<>BLANK()))

 

What do I modify it to please?

Sean
Community Champion
Community Champion

Trim and Clean the Column in the Query Editor and then Replace with null

 

QE - Clean and Trim.gif

 

Hope this helps! Smiley Happy

Didn't work.  Good effort though.

Hi @SachinC,

It's weird, I am unable to reproduce why you get the error result. Could you please share your .pbix file for further analysis?

Best Regards,
Angelia

Unfortunately there's a lot of confidential data in my PBIX file. How else can you help me please? Thanks.

Hi, I've managed to anonymise the data. How do I upload an Excel sheet please?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.