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

[ShaGuna32]

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/...

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

[lbendlin]

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-...
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/1447...

[ShaGuna32]

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=11597... https://docs.google.com/spreadsheets/d/1IGKIIruuhrorMDcolK1qTFCaULOED-Ks/edit?usp=sharing&ouid=11824... 

Looking forward to your reply, thanks again,

Kind Regards
ShaGuna

[lbendlin]

Can you please explain why you need the repeated rows?

[ShaGuna32]

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

[lbendlin]

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

[ShaGuna32]

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. 

[lbendlin]

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

see attached

[ShaGuna32]

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?

[lbendlin]

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

[ShaGuna32]

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