cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

Accepted Solutions
Highlighted
Super User II
Super User II

Re: Making calculated field from matrix columns

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
Highlighted
Community Champion
Community Champion

Re: Making calculated field from matrix columns

@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

Highlighted
Super User II
Super User II

Re: Making calculated field from matrix columns

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

Highlighted
Helper I
Helper I

Re: Making calculated field from matrix columns

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

Highlighted
Helper I
Helper I

Re: Making calculated field from matrix columns

Hi Jimmy,

Unfortunatley it is not working. Can you see my above reply and file I have attached for reference.

Highlighted
Super User IV
Super User IV

Re: Making calculated field from matrix columns

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

 


______________


Has this post solved your problem? Please mark it as a 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.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Highlighted
Helper I
Helper I

Re: Making calculated field from matrix columns

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

Highlighted
Super User II
Super User II

Re: Making calculated field from matrix columns

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

Highlighted
Super User IV
Super User IV

Re: Making calculated field from matrix columns

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?

 


______________


Has this post solved your problem? Please mark it as a 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.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


excelwithallison.com

Highlighted
Helper I
Helper I

Re: Making calculated field from matrix columns

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors