Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello all. I am trying to duplicate something I do in excel using formulas but I am failing miserably. Hopefully, I explain it enough here that possibly someone can help me achieve my goal. Also, I am asking (4) different calculation questions in a single post. I am not sure it's allowed but it's all related.
Table1 is a static table getting refreshed/updated via a CSV file.
Table1 | |
Project | Dollars |
AAA | $2.00 |
BBB | $4.00 |
CCC | $7.00 |
BBB | $2.00 |
AAA | $6.00 |
AAA | $4.00 |
Table2 is a calculated table where I want to perform the calculations.
The Unique Projects column seems to be OK however I have no idea how to achieve the rest of the needed calculations.
Here is what I am using for a unique projects list. (although I am not sure I built my initial Table2 correctly by starting it off with this first column)
Table2 = DISTINCT(Table1[Project])
SUMIF - I need to perform a SUMIF, summing up the Dollars from Table1 if the Project number matches in Table2.
Project Occurrence - I need to count the number of times a Project appears in Table1.
Sum/Occurrence - I need to divide the SUMIF result by the Project Occurrence result line by line down the table.
Table2 | |||
Unique Projects | Sumif | Project Occurrence | Sum / Occurrence |
AAA | $12.00 | 3 | $4.00 |
BBB | $6.00 | 2 | $3.00 |
CCC | $7.00 | 1 | $7.00 |
Solved! Go to Solution.
@PeeWhy So, create a relationship between your two tables on the Project. Then just create a SUM measure for summing Dollars in Table1. Create a COUNT measure for summing Project column in Table1. That should be all there is to it. Oh, and create another measure the multiplies those two measures.
@PeeWhy So, create a relationship between your two tables on the Project. Then just create a SUM measure for summing Dollars in Table1. Create a COUNT measure for summing Project column in Table1. That should be all there is to it. Oh, and create another measure the multiplies those two measures.
Thank you so much. I didn't even think about making a relationship between the two tables. That small step along with 3 different measures gives me exactly what I need.
User | Count |
---|---|
77 | |
74 | |
63 | |
61 | |
45 |
User | Count |
---|---|
108 | |
102 | |
93 | |
83 | |
64 |