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
femcoldt
Frequent Visitor

Need help with DAX

Hi everyone,  I am in a situation. the screen shot below is in excel, i have all the measures except for the ones in the red rectange. I am fighiting to get a way to calcualte this in powerbi. 

femcoldt_1-1701184234512.png

 

the YOY in excel is calculated as =SUM(K8/K3)-1

Quaterly YOY is calculated as =(SUM(K8:M8)/SUM(K3:M3))-1

YTD/YTG is calculated as =(SUM(K8:M8)/SUM(K3:M3))-1.

 

Any help is appreciated

13 REPLIES 13
femcoldt
Frequent Visitor

HI @v-shex-msft Xiaoxin,

 

FInd attached a sample PBIX file from this link Master.pbix with some random dataset about my thoutght process.

 

femcoldt_0-1701560456259.png

I want to to the first three month in the year for F24 Act + Fcst divided by F24 Actual whihc is Jan to March and so on.

 

Thanks

Hi,

Share some dummy data in an Excel file and on another tab, show the expected results with your formulas.  I will try to translate those Excel formulas in the DAX language.  Share only as much data as is required to get those 3 calculations.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur 

 

Find the link to the excel dummy excel_dummy.xlsx

 

Thank you for your help

There is no concept of cell merging in visuals.  Therefore, the workaround would be to insert quarter and half year columns and show the ro 14 and 15 calcultions under those columns.  If you are OK with my approach, then share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur , thanks. I already attached the PBIX file link already. Here it is again

Master.pbix 

https://1drv.ms/u/s!Arru7WEvbiAovkneIrRXoKExUcwn?e=JDoQeJ

Hi,

PBI file attached.  All the growth rates appear in 1 row itself (YoY).

Hope this helps.

Ashish_Mathur_0-1701664524797.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur , thanks so much for your help. This is well appreciated.

 

I still cant fid the values for the YTD/YTG from the results or am i looking at the figures wrongly

femcoldt_0-1701701014460.pngfemcoldt_1-1701701049283.png

Thanks

Share the download link of the MS Excel file again.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur . Here it is excel_dummy.xlsx

Hi,

Why is the YTD/YTG calculation from Jan-Aug/Sep-Dec?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur it is a business metric that the business uses. I dont know why they do that

Hi,

Unless i know the logic of the month selection, i cannot help.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-shex-msft
Community Support
Community Support

Hi @femcoldt ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

BTW, power bi did not exist row and column index, so you can't simply calculate across multiple fields or to accurately calculate on specific cells. You need to use different field as filter to limit the calculate ranges. (notice: recursion calculation not supported in Dax)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.