Forum Discussion

Mughees's avatar
Mughees
Helper II
5 years ago
Solved

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 ?

Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    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

9 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    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

    • Mughees's avatar
      Mughees
      Helper II
      5 years ago

      Hi Jimmy,

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

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        5 years ago

        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

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    5 years ago
    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.