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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors