Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I have to add alot of calcualted fields in my data set that need to be created from the value columns of the matrix. However, I am unable to do that. I tried to apply some formulas but they were of no use, since the slicer I am using is of years (2017, 18, 19, 20) and it will change value and calculated field of the matrix. Below is the data.
DISTRICT ADEQ INADEQ
ATTOCK | 36 | 5 |
BAHAWALPUR | 88 | 9 |
BAHWLNAGAR | 86 | 9 |
Calculated field (36/(36+5))= 88% ((Adeq)/(Adeq+Inadeq))
The data that I have has 20,000 rows nearly and is in such kind of format
YRONSET | DISTRICT | EPID | ADEQ |
2018 | RYKHAN | PB/72/18/002 | ADEQ |
2018 | RAWALPINDI | PB/11/18/001 | Inadeq |
2018 | SIALKOT | PB/25/18/001 | ADEQ |
2019 | Bahawalnagar | PB/72/18/001 | Inadeq |
2018 | RAWALPINDI | PB/11/18/003 | ADEQ |
2018 | SIALKOT | PB/25/18/004 | Inadeq |
Can you please guide the right way to get it done ?
Solved! Go to Solution.
Hello @Mughees
i think you have just do create two measure that counts the rows of your table, filtering one time for adeq on time for inadeq. After that create one more measure to make a calculation of the 2 new created measures.
Find enclosed the file
Hope this helps
Jimmy
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi Allison,
I tried the command but it is also not giving the desired result.
Please see below the two files for reference.
https://drive.google.com/file/d/1H75UmbJgdy1x39VjyDLP1dIc3AMh-UPq/view?usp=sharing https://drive.google.com/file/d/1JqcbRFz_wC1u8OvCO3OTXQCr4yNLL1ez/view?usp=sharing
If you see the sheet intended outcome sheet in excel file, I want to get calculated item mentioned "stool adeq." in that sheet in my power BI matrix. Moreover, there will be couple of slicer i.e. 5-10 that I will apply in POWER BI that will keep on changing the values of adequate and inadequate at district level. Such kind of calcualted item I am unable to create in POWER BI.
I hope I make my question clear this time
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hello @Mughees
i think you have just do create two measure that counts the rows of your table, filtering one time for adeq on time for inadeq. After that create one more measure to make a calculation of the 2 new created measures.
Find enclosed the file
Hope this helps
Jimmy
Hi Jimmy,
Unfortunatley it is not working. Can you see my above reply and file I have attached for reference.
Hello @Mughees
did you see my file? To me it seems that's exactly working as your Excel-file. You have to add for sure a slicer for years, and in case fix the date-column somehow in month, that you are able to filter them to. But the main question, how to make the measures was answerd
BR
Jimmy
Hi Jimmy. Thanks a million. You just gave the right solution. I was out in field work so I could not possibly look into it more deeply.
@Mughees
Not very clear about the source data and the expected output.
Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?
_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi Fowmy, PFB the link to POWER BI file and Excel file
https://drive.google.com/file/d/1H75UmbJgdy1x39VjyDLP1dIc3AMh-UPq/view?usp=sharing https://drive.google.com/file/d/1JqcbRFz_wC1u8OvCO3OTXQCr4yNLL1ez/view?usp=sharing
Kindly note that I want to make calcualted item from the matrix value in the power BI, similar to the one I made in the excel file. Moreover, I want to keep slicer as well so that the adeq and inadeq. value keeps on changing based on year (multiple years in real file), month, quarter, district etc. Regards