New Table / Row Calculation

[Anonymous]

Hello

I am new to Power BI DAX. I hope to be able to get help with this.

I created a New Table using UNION and added Row GM (Gross Margin), summing up all rows of AVK Total.

Is there a way to apply a filter in the expression, if I selected Project eg. P18-1042 then the Row GM is calculated only for P18-1042 ie. 17,000,000?

GM = UNION('GM-Value',ROW("Project No", "", "Category", "GM", "AVK Total", VALUE(SUM('GM-Value'[AVK Total]))))

Project          Category          AVK Total

                     GM                    51,000,000

P18-1042      Cost                 -13,000,000

P18-1042      Contract Rev    30,000,000

P18-1043      Cost                 -26,000,000

P18-1043      Contract Rev     60,000,000

Thanks in advance

Lee

[rsaprano]

Hi @Anonymous 

Showing a value for a specific projects is the default for Power BI - there's no need to specify a complicated query to achieve this. The union of two data tables would be easier in the query editor rather than a calculated DAX table and you just need to end up with a table that has Project, Category and Value in it. You can then create a measure to sum the value and when you put this measure in a table or visual against Project/Category it will automatically show you the total value for theat project/category combination.

Might i suggest the following two free online courses to get started with shaping your data/creating some simple calculations?:

https://www.sqlbi.com/p/introducing-dax-video-course/

https://www.sqlbi.com/p/introduction-to-data-modeling-for-power-bi-video-course/

Thanks

Rishi

[Anonymous]

Dear Rishi

The tutorial you recommended is very helpful. I have simplified and progress a little ie. Using Measures to calculate GM and GM%.

However, i have a problem with GM%. Do you know how I can apply filter context so that it shows the correct % by project?

Thanks

Lee

[rsaprano]

Hi Lee,

It's hard to say without seeing exactly what you're trying to achieve - is it the proportion of GM for that project as a proportion of that for all projects?

In this case generally the denominator for your GM% (where the numerator is say a measure called [GM]) is something like CALCULATE([GM],ALL(Project Table[Project]))

[Anonymous]

Hi Rishi

I had managed to get the result I wanted using Measures as you suggested. Thanks for this!

But now, I faced with computing resource issue. I only have 48 Meassures as of now.

Processor i5 with 8GB RAM.

Rgds

Lee

[Anonymous]

Thanks for your reply. I will go through the training as you suggested.

What I wrote early was a simplified explanation. The actual format is quite complicated. As below.

I need to calculate the Gross Margin, Gross Margin % and POC in Rows.

Rgds

Lee