topic Re: Power BI Divide Measure not ignoring the Blank rows in DAX Commands and Tips

Power BI Divide Measure not ignoring the Blank rows

shubhamamilkant — Fri, 29 Jan 2021 21:35:34 GMT

Hello, I have spent few hrs on this and I am not sure how to solve this problem, I want Divide this 2 measures, Rolling 6 month Gross Profit by Total Gross Profit past 12 months, u can see thare are few blanks in both columns, I want ignore this to get a Final value.

Currently as per the image - Divide value is 79.61 (4,132,822/5,191,196) but if u filterout blank rows it will be 73,17% (2,844,347/3,887,309), I would like to see 73.17% as a value in card, but no matter what formula i used to divide (like fiter using blank condition, using if condition, it is always showing me 79.61 % )

can some one help to write correct formula to get 73.17% in card, In table I can get 73.17% by using the same not blank filter

Thanks in advanced.

This is what i am getting -

This is what i need - but instad of table in card (73.17)

Re: Power BI Divide Measure not ignoring the Blank rows

themistoklis — Fri, 29 Jan 2021 21:36:31 GMT

shubhamamilkant

Can you share the formula that you use in the card and also in the table?

If you could share the pbix file that would be even better. Do not send any sensitive data

Re: Power BI Divide Measure not ignoring the Blank rows

shubhamamilkant — Fri, 29 Jan 2021 21:54:55 GMT

When I am trying to reply I am receiving this error, any idea ( i am not able to copy any formula because of this)

Re: Power BI Divide Measure not ignoring the Blank rows

shubhamamilkant — Fri, 29 Jan 2021 22:08:40 GMT

themistoklis
Formula i have used is -

New GgPR 2021 = DIVIDE([Rolling 6 months Gross Profit],[Total Gross Profit rolling past 12 months]))

another formula i tried is,
New GgPR 2021 = IF(NOT(ISBLANK([Rolling 6 months Gross Profit])) && NOT(ISBLANK([Total Gross Profit rolling past 12 months])), DIVIDE([Rolling 6 months Gross Profit],[Total Gross Profit rolling past 12 months]))

another one i used, not the exact syntax but - if(rolling 6 month <> blank(),if(Total Gross profit past 12 month <> blank(), Divide ([Rolling 6 months Gross Profit],[Total Gross Profit rolling past 12 months])
This all 3 formulas gave, exact same result 79.61 %

I am not sure, If i can share Pbix file, but i hope this helps to understand

Re: Power BI Divide Measure not ignoring the Blank rows

themistoklis — Sat, 30 Jan 2021 14:23:59 GMT

Hello shubhamamilkant ,

Could you please share the formulas that you use for measures:

Rolling 6 months Gross Profit, Total Gross Profit rolling past 12 months

Also in the table you have only 1 dimension called 'Program/ACCT'?

Re: Power BI Divide Measure not ignoring the Blank rows

shubhamamilkant — Mon, 01 Feb 2021 01:25:21 GMT

themistoklis Sorry for the delay in the response
Let me start again, to give little bit more information -
I have 2 tables, 1) load_summary Table and 2) Date Table

1)load_summary Table has 3 columns Program/Acct, load_date and Total Gross Profit
2) Date table is created by me -

CALENDAR(MIN(load_summary[load_date]),MAX(load_summary[load_date]))

I want to calculate New GgPR 2021 (which is last 6 month gross Profit Divide by 12 months ago (6 months rolling Profit)

Assuming Today is Feb 1 - last 6 month Gross profit will be from 8/2/2020 to Today
12 months ago (6 months rolling Profit) = 02/1/2020 to 8/1/2020 (6 months profit but from from 12 months)

Measure Rolling 6 months Gross Profit =

var first_date_2021 = TODAY()
var end_date_2021 = EDATE(TODAY(),-6)
return
CALCULATE([Total Gross Profit],DATESBETWEEN( 'Date Table'[Date], end_date_2021, first_date_2021))

Measure
Total Gross Profit rolling past 12 months =
var first_date_2021 = EDATE(TODAY(),-12)
var end_date_2021 = EDATE(TODAY(),-6)
return

CALCULATE([Total Gross Profit], DATESBETWEEN('Date Table'[Date], first_date_2021, end_date_2021))

What I want to calculate is % of gross profit only when we had business (acct/program) in both measures (rolling 6 months / Gross Profit rolling from 12 months to 6)

I really appreciate all the help, I hope this clears my question and Thanks in advance