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.
Hi,
I'm trying to use an IF statement within a Table visual and I'm having issues with the total column.
Margin Measure = CALCULATE(SUMX(FILTER(Sheet1, Sheet1[Agent] = "John"), Sheet1[Margin]), USERELATIONSHIP('Date'[Full Date], Sheet1[Date]))
Total = IF([Margin Measure] >=100, 1000, BLANK())
Those are the measure that I use in this table:
As you can see, the total isn't correct. It this case the total should be 2000
Does anyone know how to fix this?
https://www.dropbox.com/s/yl5ypb8tz8asim7/help%20-%20Copy.pbix?dl=0
Thanks
Solved! Go to Solution.
@michael_knight
Do a crossjoin:
Total =
SUMX(
CROSSJOIN( VALUES(Agents[Agent]),VALUES('Date'[Month/Year])) ,
IF([Margin Measure] >=100, 1000, BLANK())
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@michael_knight
You can make your relationship active between Date and Sheet tables and modify the measure as below:
Total =
SUMX(
SUMMARIZE(Sheet1,Agents[Agent],'Date'[Month/Year]),
IF([Margin Measure] >=100, 1000, BLANK())
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi @Fowmy thanks for the response, I need to have the relationship inactive. I tried to recreate my main dataset as best as possible, as I'm using multiple date columns with the Date table I needed to have them inactive
Do you know how I'd be able to do the measure you suggested but with the relationship inactive?
@michael_knight
Do a crossjoin:
Total =
SUMX(
CROSSJOIN( VALUES(Agents[Agent]),VALUES('Date'[Month/Year])) ,
IF([Margin Measure] >=100, 1000, BLANK())
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Good stuff, thank you!
Out of curiosity, will this measure work with Switch(True() ?
@michael_knight This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
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 |
---|---|
112 | |
97 | |
82 | |
67 | |
61 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |