Failing at creating DAX measure calculation :-(

[ConfusedMum]

Hi all,

I am very downhearted here, as this was my first time having a go at using Power Pivot using Excel and creating a data model, etc...

I have two tables in my data model:

1. Group Grants - name of group, ID of group, total grant allocated so far this year.
2. Vouchers Paid - voucher ID, cost of voucher, a variety of other data, name of group voucher allocated to & ID of group voucher allocated to.

The data connection between the two tables is the group ID.

I created my first Power Pivot Table containing:

• Name of Group (table 2)
• Number of vouchers used (table 2)
• Total cost of voucher value (table 2)
• Total grant so far (table 1)

I congratulated myself too soon....

I then wanted to create a DAX measure to work out Remaining Grant and this is where I have totally failed

I REALLY want to learn how to do this myself, but when you watch LinkedIn Learning or YouTube videos they just type a formula in and I don't understand what they are using and why.

I am guessing that it is some kind of SUM formula, but I honestly don't know and am just getting myself more and more confused.

Two questions please: a) what measure formula would you recommend and b) is there a list of DAX measure/formulas, how they work and when you should use each formula.

(I am using Excel on my Windows 10 PC, and it is the desktop version of Excel, through Microsoft 365 Apps for enterprise.)

Thank you SO much in advance for any suggested formulas and pointers about where to learn more.
Cathy

 

[Matt Allington]

If you want to learn yourself, you can start at my website, here

Knowledge Base

This Knowledge Base provides a one-stop shop of information to help people like you get started (and grow to love) Power BI, Power Query, and Power Pivot.

exceleratorbi.com.au

I recommend you read (in order)

The Optimal Shape for Power BI Data

It is common for self taught Power Pivot users to get the shape of their data all wrong. This article describes the optimal way to do it.

exceleratorbi.com.au

Calculated Columns vs Measures in DAX

This article looks at Calculated PowerPivot Columns vs Measure ✅ PowerPivot expert, Matt Allington, explains the No.1 mistake he sees in self taught DAX students with an Excel background is the over use of PowerPivot Calculated Columns instead of Measures. This is an excellent article with...

exceleratorbi.com.au

Power BI Skills - Thinking of Data in Tables

Level: Beginners One very important concept that Excel users often struggle with when starting on their Power BI and Power Pivot journey is that they need to learn to think about data in tables rather than thinking about data in cells. In the spreadsheet world every cell can be a [...]Read More »

exceleratorbi.com.au

After reading these 3, you should have a better idea of what you need to do.

Then with your model, follow the general concept of the dimension table being used to slice and dice your data (add columns from the table to rows in your pivot) and do the calculations on your fact table. You may need to do a calculation on your dim table, too, if your grant is stored in that table.

When you write your measure, set up a pivot table and add the measures to the pivot to check they are working

Post back with your progress