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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
ShaGuna32
Helper I
Helper I

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

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

ShaGuna32_0-1697007295425.png

 

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

10 REPLIES 10
lbendlin
Super User
Super User

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

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

Can you please explain why you need the repeated rows?

 

lbendlin_0-1697229325776.png

 

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

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

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. 

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

 

lbendlin_0-1697239990033.png

see attached

 

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?

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

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.