cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Sethforte1
Frequent Visitor

Converting Excel to DAX

Hello All,

 

I'm not sure if this is as hard as I am making it out to be, but I keep striking out. I have been tasked with converting this Excel Dashboard to a PowerBI report and the sheet on "QTC AVG Completion" is just throwing me off beyond measure. Still fairly new to PowerBI and quite frankly still fairly new to Excel and Vlookups and Power Pivots.

 

Can someone please assist me with a DAX formula 

to produce the graph I am seeing on the excel dashboard? Right now all I am able to do is calculate the sum of the completed assignments by month and show that. I can tell there is a lot of back end dynamic excel funcitions happening on the "Assignment Analysis" tab to produce their "QTC AVG Completion" graph. I have attached the excel sheet and copy of the Power BI report in a google drive for reference. 

 

https://drive.google.com/drive/folders/1aH1MKakjajjdsDuO5380vrTKH2P-_SnF?usp=sharing

 

2020-02-22 10_04_22-Window.png

 
 
 
 
1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Converting Excel to DAX

Hi @Sethforte1 ,

 

First of all your explanation is not the best one and your dataset is very complex to achieve any results withouth understanding what you want to have.

 

As a good practice check please see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 (courtesy of @Greg_Deckler).

 

Picking up your two table you refer I have made the following measure:

 

Measure =
DIVIDE (
    COUNT ( tbl_Import[Complete] );
    CALCULATE (
        SELECTEDVALUE ( tbl_DaysCount[Working Days-Holidays] );
        FILTER (
            tbl_DaysCount;
            tbl_DaysCount[Month] = MAX ( tbl_Import[Complete Month] )
        )
    )
)

 

This is not the optimal measure and need further explanation to help you better, and believe that the first step is to get a better data model in place.

 

Check the PBIX file with the chart and if it's working properly, if you can also give better insights would be helpfull


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog:

Power BI em Português





View solution in original post

1 REPLY 1
Super User III
Super User III

Re: Converting Excel to DAX

Hi @Sethforte1 ,

 

First of all your explanation is not the best one and your dataset is very complex to achieve any results withouth understanding what you want to have.

 

As a good practice check please see this post regarding How to Get Your Question Answered Quickly:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 (courtesy of @Greg_Deckler).

 

Picking up your two table you refer I have made the following measure:

 

Measure =
DIVIDE (
    COUNT ( tbl_Import[Complete] );
    CALCULATE (
        SELECTEDVALUE ( tbl_DaysCount[Working Days-Holidays] );
        FILTER (
            tbl_DaysCount;
            tbl_DaysCount[Month] = MAX ( tbl_Import[Complete Month] )
        )
    )
)

 

This is not the optimal measure and need further explanation to help you better, and believe that the first step is to get a better data model in place.

 

Check the PBIX file with the chart and if it's working properly, if you can also give better insights would be helpfull


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog:

Power BI em Português





View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors