topic Re: How to calculate without blank in DAX Commands and Tips

How to calculate without blank

Anonymous — Sun, 23 Oct 2022 07:25:29 GMT

Re: How to calculate without blank

Anonymous — Sun, 23 Oct 2022 07:35:45 GMT

Dear all,

I am using Power BI Desktop, update September 2022.
I need to know how many line of oustanding items that will delivered within this week, next week, next 3 weeks, ans so on.
I group them into:
1w
2w
3w
No PO (purchase order)

But in this column, I have also blank value, which is if the material already received.

How do I calculate ignoring blank, I don't want the blank visualize.

Thank you very much.
Prima Indonesia

Re: How to calculate without blank

Anonymous — Sun, 23 Oct 2022 09:27:55 GMT

Hello null values usually dont show on visulas and dont get calculated, so im guessing you changed null to a space or empty column? when you have the type of the column as text, blank will be used as a space like so if thats the case then just filter out the "blank" by doing this

Countof = CALCULATE(COUNT(ETA_Table[ETA Grouping]),FILTER(ETA_Table,ETA_Table[ETA Grouping]<>""))

Re: How to calculate without blank

Anonymous — Sun, 23 Oct 2022 20:55:29 GMT

Dear eliasayy,

Thank so much for you kind, help and attention.
My problem solved.

Again, thank you.
Prima Indonesia

Re: How to calculate without blank

Anonymous — Sun, 23 Oct 2022 21:19:23 GMT

Dear eliasayy,

The solution given to me working well, but then how do I get the percentage except/ignoring the blank?

% ETAGroup = [Count ETA]/CALCULATE([Count ETA],ALL(Table2[OWL Type]))

With the above measure, it gave me the wrong result.

And below measure based on your suggestion is working well.

Count ETA = CALCULATE(COUNT(Table2[ETA Grouping]),FILTER(Table2,Table2[ETA Grouping]<>""))

Thank you very much.
Prima Indonesia

Re: How to calculate without blank

Anonymous — Sun, 23 Oct 2022 22:06:51 GMT

hmm im not sure if i understood correctly if you could share some sample data or something to better understand

Re: How to calculate without blank

Anonymous — Sun, 23 Oct 2022 22:18:16 GMT

Dear eliasayy,

What kind of data sample do you want? is it Excel workbook or PBI file?

Thank you
Prima Indonesia

Re: How to calculate without blank

Anonymous — Sun, 23 Oct 2022 22:19:21 GMT

anything will do i just need to see the data to know what you mean

Re: How to calculate without blank

Anonymous — Sun, 23 Oct 2022 22:26:05 GMT

Dear eliasayy,

Is it possible to upload the sample of Excel workbook (16KB or Power BI files (2MB) here. What is the icon should I use?

Thank you
Prima

Re: How to calculate without blank

Anonymous — Sun, 23 Oct 2022 22:27:34 GMT

for normal users like us we cant directly share files on powerbi. so for us to share
you need to upload the file to onedrive dropbox or whatever sharing cloud site and copy the link and share it here

Re: How to calculate without blank

Anonymous — Sun, 23 Oct 2022 22:35:35 GMT

Dear eliasayy,

I am very glad you back again to save me.
Below is the link of Power BI file:
https://drive.google.com/file/d/1GQDxDovBQ-cYTiXqMtUHH3cB40vwM0Di/view?usp=sharing

Thank you very much.
Prima

Re: How to calculate without blank

Anonymous — Sun, 23 Oct 2022 22:40:00 GMT

Dear eliasayy,

Below is the link of Excel workbook.
https://docs.google.com/spreadsheets/d/1nCXE6NJJS9IkYXSzJFSmHTHEZPkjpMjh/edit?usp=sharing&ouid=116742594743234602268&rtpof=true&sd=true

Thank you very much
Prima

Re: How to calculate without blank

Anonymous — Sun, 23 Oct 2022 22:40:04 GMT

no problem now can you please explain what youre trying to achieve? you want the count of eta without blanks to divid over total?

Re: How to calculate without blank

Anonymous — Sun, 23 Oct 2022 22:53:39 GMT

kindly use this measure:

perc = [Count ETA]/CALCULATE(COUNT(Table2[ETA Grouping]),FILTER(ALL(Table2),Table2[ETA Grouping]<>""))

by the way great work on design just peice of advice, try to create a metric table and put all your measures in it, its easier and more organized you can do that by clicking on "Enter data" tab and just naming the table metrics and after that add metrics to it and you can delete the blnak column

Re: How to calculate without blank

Anonymous — Mon, 24 Oct 2022 01:58:06 GMT

Dear eliasayy,
Thanks for everything. Thanks for your kind attention.
May God bless you.

Warm regards,
Prima Indonesia