Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Long time user and I've searched all around the web and the forums for an answer but I'm stumped. I'm having trouble getting a measure to sum up to the parent level in my matrix visual. So the measure is composed of a couple of different formulas. Here's what the sample data looks like:
I'm also using a date table.
Running Total Current Quarter:
CALCULATE(SUM(Sheet1[Amount]),DATESQTD(DateTable[full_date]))
Final Total Q1 Prior Year: CALCULATE(SUM(Sheet1[Amount]),DATESBETWEEN(DateTable[full_date],DATE(YEAR(TODAY())-1,1,1),DATE(YEAR(TODAY())-1,3,31)))
Non Returning Dollars:
IF([Running Total Current Quarter]<=0,[Final Total Q1 Prior Year]*-1,BLANK())
So the Matrix has the dates for the quarter as columns across the top as shown below:
What I'm trying to do is get the totals in Non Returning Dollars to sum up to the Station level from the Account level. I understand that the measure technically is false at the Station level. I've tried solving this problem by using SUMX in a variety of ways, yet none of them accomplish the behavior I'm looking for. The formula I initially assumed would work would be:
SUMX(VALUES(Sheet1[Account]),[Non Returning Dollars])
In the picture I attached the Non Returning Dollars calc should ideally show -9973 at the subtotal level for station A since accounts EE and HH haven't billed for any money at that point in the quarter.
I've tried a ton of different formulas but I can't crack this. I'd appreciate any help and can elaborate on any further formulas I've tried or provide more sample data to work with.
Try using SUMMARIZE Function instead.
I tried a couple of different variations of SUMMARIZE and they weren't returning any values. I tried the two formulas below:
Measure: SUMX(SUMMARIZE(Sheet1,Sheet1[Station],Sheet1[Account]),[Non Returning Dollars])
Measure 2: SUMX(SUMMARIZE(Sheet1,Sheet1[Station],Sheet1[Account],Sheet1[Date]),[Non Returning Dollars])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |