cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nleuck Regular Visitor
Regular Visitor

DAX Formula

Hello All,

 

I'm currently trying to recreate a formula in Power BI that is being used in Excel for a report. On the image below I've place an M above the columns of my table that represent Measures I created. Sales Amount is a column that has a Default Summarization of Sum. I need a formula that looks like this:

 

(total of Prev Period Gross Margin $, subtract Prev Period Gross Margin $, add Gross Margin $)

 divide--------------------------------------------------------------------------------------------------

(total of Prev Period Sales Amount, subtract Prev Period Sales Amount, add Sales Amount)

 

(275,446 - 4915 + 300) / (1,138,962 - 50,060 + 2,409) -----> 270,831 / 1,061,311 ------> .24817 ------> 24.82%

 

Any help would be greatly appreciated!!!

 

Thanks,

 

power screen shot.png

5 REPLIES 5
Community Support Team
Community Support Team

Re: DAX Formula

@nleuck,

 

Create a measure and try DAX formula like pattern below:

 

Measure =
CALCULATE ( SUM ( Table[Prev Period Gross Margin] ), ALL ( Table ) )
    - MAX ( Table[Prev Period Gross Margin] )
    + MAX ( Table[Gross Margin] )
        / CALCULATE ( SUM ( Table[Prev Period Sales Amount] ), ALL ( Table ) )
    - MAX ( Table[Prev Period Sales Amount] )
    + MAX ( Table[Sales Amount] )

Regards,

Jimmy Tao

 

nleuck Regular Visitor
Regular Visitor

Re: DAX Formula

@v-yuta-msft

 

You can't SUM on measure. Thanks though.

Community Support Team
Community Support Team

Re: DAX Formula

@nleuck,

 

Could you share the original table for further test?

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team
Community Support Team

Re: DAX Formula

@nleuck,

 

Have you solved your issue by now? If you have, could you please help mark the correct answer to finish the thread? Your contribution will be much appreciated.

 

Regards,

Jimmy Tao

nleuck Regular Visitor
Regular Visitor

Re: DAX Formula

@v-yuta-msft

 

There wasn't a correct answer. It looks like what I'm trying to do can not be done in Power BI.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 330 members 3,262 guests
Please welcome our newest community members: