topic Re: DAX QUERY to Use the Previous column value or the Grand total value in the empty column cells. in DAX Commands and Tips

DAX QUERY to Use the Previous column value or the Grand total value in the empty column cells.

ShaGuna32 — Wed, 11 Oct 2023 06:59:41 GMT

Hi Everyone

I have attached a link to the previous issue thread for your better understanding,
Link: https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Measure-to-Calculate-Percentage/m-p/3445249#M131063

I have achieved results as follows

Now,
I would like to use the Grand Total average values in the empty cells in the column, or the values above or below in the column.

I tried to use MAX, Earlier functions but they are used only if I have a calculated column. I have created a measure for the previously suggested formula. Tried to create a calculated column but isn't working correctly.

Any ideas for the same will be highly appreciated,
Looking forward to any ideas and suggestions, thanks.

Kind Regards
ShaGuna

Re: DAX QUERY to Use the Previous column value or the Grand total value in the empty column cells.

lbendlin — Fri, 13 Oct 2023 01:47:58 GMT

DAX has no concept of "previous column". Unpivot your data to make it suitable for processing in Power BI.

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: DAX QUERY to Use the Previous column value or the Grand total value in the empty column cells.

ShaGuna32 — Fri, 13 Oct 2023 06:36:22 GMT

Hi lbendlin ,

Good day,
Thanks for reaching out, much appreciated.

As advised, I have attached a document for your kind reference with the sample raw data and achieved and expected results.

DAX Formula used to Calculate the Material Grade %,

M % = VAR _vendor = SELECTEDVALUE ( 'Table'[Vendor] ) VAR _per = COUNT ( 'Table'[Material grade] ) VAR _total = CALCULATE ( COUNT ( 'Table'[Material grade ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Vendor] = _vendor ) ) RETURN DIVIDE ( _per, _total )

Trying to: For those Material grade % in a vendor are empty I wanted to use that particular vendor's total average values in the empty material grade % for those product racks. (Refer to Attached document)
https://docs.google.com/spreadsheets/d/1Rg0ZHW4G36sxZXdCjneHENBEu2pm-XA_/edit?usp=sharing&ouid=115971939741028185369&rtpof=true&sd=true https://docs.google.com/spreadsheets/d/1IGKIIruuhrorMDcolK1qTFCaULOED-Ks/edit?usp=sharing&ouid=118247509732557767896&rtpof=true&sd=true

Looking forward to your reply, thanks again,

Kind Regards
ShaGuna

Re: DAX QUERY to Use the Previous column value or the Grand total value in the empty column cells.

lbendlin — Fri, 13 Oct 2023 20:35:32 GMT

Can you please explain why you need the repeated rows?

Re: DAX QUERY to Use the Previous column value or the Grand total value in the empty column cells.

ShaGuna32 — Fri, 13 Oct 2023 22:21:49 GMT

Hi lbendlin ,

Sorry, That's my mistake.
The same Product racks won't repeat, I just wanted to show my expected result.

I appreciate your time and effort, looking forward to some ideas, thanks.

ShaGuna

Re: DAX QUERY to Use the Previous column value or the Grand total value in the empty column cells.

lbendlin — Fri, 13 Oct 2023 22:35:06 GMT

Please restate your expected outcome based on the sample data you provided.

Re: DAX QUERY to Use the Previous column value or the Grand total value in the empty column cells.

ShaGuna32 — Fri, 13 Oct 2023 22:44:08 GMT

I have calculated Material Grade% for those Product racks which has historical material grade using the above formula. But there are some new products in the data, which has no historical data. So, I want to use that vendor's Average material grade % ( Column grand total in the sample data) for those product racks which has no material grade %.
It will allow me to process my report further analysis.

Re: DAX QUERY to Use the Previous column value or the Grand total value in the empty column cells.

lbendlin — Fri, 13 Oct 2023 23:33:45 GMT

To report on things that are not there you need to use disconnected tables and cross joins.

see attached

Re: DAX QUERY to Use the Previous column value or the Grand total value in the empty column cells.

ShaGuna32 — Fri, 13 Oct 2023 23:58:17 GMT

The material Grade % is a measure, Due to large data the calculated column is not providing the expected result. So I have created it as a measure. The DAX formula you have suggested will work if the material grade % is a column. I cannot sum or average the measure.
That's is biggest issue here! any ideas on this?

Re: DAX QUERY to Use the Previous column value or the Grand total value in the empty column cells.

lbendlin — Sat, 14 Oct 2023 00:01:40 GMT

Use the measure as is ( remove the aggregation functions) or - better - recreate the business logic in the new measure.

Re: DAX QUERY to Use the Previous column value or the Grand total value in the empty column cells.

ShaGuna32 — Tue, 17 Oct 2023 01:35:34 GMT

hi lbendlin ,

The proposed method didn't work.

I have to remodel the entire work from scratch and will try again. I will keep you posted.
Thanks
ShaGuna