Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mughees
Helper II
Helper II

Making calculated field from matrix columns

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

ATTOCK365
BAHAWALPUR889
BAHWLNAGAR869

 

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

 

YRONSETDISTRICTEPIDADEQ
2018RYKHANPB/72/18/002ADEQ
2018RAWALPINDIPB/11/18/001Inadeq
2018SIALKOTPB/25/18/001ADEQ
2019BahawalnagarPB/72/18/001Inadeq
2018RAWALPINDIPB/11/18/003ADEQ
2018SIALKOTPB/25/18/004Inadeq

@https://ibb.co/thdCn51 

 

Can you please guide the right way to get it done ?

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

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

View solution in original post

9 REPLIES 9
AllisonKennedy
Super User
Super User

@Mughees
I am not sure if I understand your question, so let me restate:
You want to create a calculation that updates based on slicer selection - this must be done as a calculated MEASURE (not column).
You want to calculate percent of total - this must be done using filter expressions.

For example, you could try this:
Percent Adeq = DIVIDE( COUNT(data[YRONSET]), CALCULATE(COUNT(data[YRONSET]), ALL(data[ADEQ])))

To calculate the percent of Adeq/InAdeq for each category.

If you only want it to show percent Adeq, you can add another CALCULATE(count(yronset), Adeq="Adeq")
Note there's a few syntax errors in that second expression, it's just meant to give you the idea.

If I have understood your question correctly, let us know and if my answers still don't work for you please provide screenshots or examples of what is going wrong.

Please @mention me in your reply if you want a response.

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

The intended outcome sheet in your excel file is not loading properly for me - I just see a simple table. Are you able to post a screenshot maybe please?

Please @mention me in your reply if you want a response.

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

Jimmy801
Community Champion
Community Champion

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.

Jimmy801
Community Champion
Community Champion

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.

Fowmy
Super User
Super User

@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 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors