Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello all,
Building a report in which the matrix results blanks (of course) where there is not data entry for those months.
I am using an HASONEVALUE and AVERAGEX which gives me the results I want. I just need help swaping the blank cells in the matrix to present a 0% so the average is correct.
hi @Anonymous
for your case, you should need to add a if conditional in the formula to judge that only replace blank with 0 which are in the current period.
please share your sample pbix file, that will be a great help.
Regards,
Lin
@Anonymous
Since you do not have data for those periods, no calculation takes place there. To get the correct average, you need to modify the measure as:
Usage Average =
IF (
HASONEVALUE ( Equip_Utl_Days[Period] ),
[Usage Percent Max 100],
DIVIDE(
SUMX(
VALUES ( Equip_Utl_Days[Period] ),
Equip_Utl_Days[Usage Percent Max 100]
),
COUNTROWS(VALUES ( Equip_Utl_Days[Period] ))
)
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
As far as I know, that blank cannot be replaced.
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Thank you.
I guess then I would create a new column in the table where you have 0 if nothing else is present for each period.
@Anonymous
That should work for you.
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@cwalraven try to use the following DAX:
VAR _avg =
Hi @Anonymous ,
Try to add the +0 to the [Usage Percent Max 100] measure and not to this final measure.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous
Add +0 to your measure and will get 0% on your cells.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |