How to calculate filling rate and show in matrix?

[Anonymous]

Hello,

I am new to PowerBI and I'm struggeling on creating a measure for the filling rate of my application portfolio. My data comes from a JSON file and after loading the file I have following columns:

I have several applications which are listed under applications.title. For every application there are 12 different information areas  and these areas have different fields. 

Now I want to create a matrix which shows me my applications as rows, the areas as columns and the filling rate of each area as values.

The filling rate should be calculated depending on relevant fields of each area. For example there are 11 fields in the information area "common", but only 9 of them shall count towards my filling rate.
My approach was to create a measure for every information area, because the relevant have to be named in the calculation. This is my approach:

commonFillRate = 
VAR neededFieldsList = {"APP ID", "Name", "Kurzname", "Hersteller", "Hersteller: Ansprechpartner", "Dienstleister", "Dienstleister: Ansprechpartner", "Beschreibung", "Links"}
VAR blankFields = COUNTAX(
    FILTER(
        AP,
        [applications.areas.fields.description] IN neededFieldsList
    ),
    ISBLANK(AP[applications.areas.fields.content])
)
RETURN blankFields / COUNTROWS(neededFieldsList)

But when I use this measue as values in my matrix the result is a column which shows 100,00% for every application. I do not understand what I am doing wrong here. Can somebody guide me please? 🙂

[Anonymous]

I have a solution now.

My report is build based on a table visual and not on a matrix visual. The example of @amitchandak helped me understand what I was doing wrong. Based on this example I created a measure for every information area and used them each as a column in my table visual. At last I created a measure to summarize the filling rate per application with a new measure which calculates the average value of all information area rates.

Thank you for your help!

[Anonymous]

I now tried to create a column "rate" which holds the value of the correct measure depending on the information area:

rate = SWITCH(
    AP[Bereich], 
    "Allgemein",  
    [commonFillRate], 
    "Organisation",
    [orgaFillRate]
)

 When I add this column as value to my matrix I get the correct columns, but the value is always 100%. I do not understand how I configure this correctly. 

[v-easonf-msft]

HI, @Anonymous 

Have you tried to convert your calulated column to  a measure as below:

rate = SWITCH(
    selectvalue(AP[Bereich]), 
    "Allgemein",  
    [commonFillRate], 
    "Organisation",
    [orgaFillRate]
)

As a workaround,you can also consider reducing the value column width to hide the columns you don't want in your first screenshot.

(Notice to turn off  the word wrap of values).

Best Regards,
Community Support Team _ Eason

[amitchandak · Accepted solution]

@Anonymous ,  Try like

commonFillRate =
VAR neededFieldsList = {"APP ID", "Name", "Kurzname", "Hersteller", "Hersteller: Ansprechpartner", "Dienstleister", "Dienstleister: Ansprechpartner", "Beschreibung", "Links"}
VAR blankFields = COUNTAX(
FILTER(
AP,
[applications.areas.fields.description] IN neededFieldsList
),
AP[applications.areas.fields.content]
)
RETURN divide( blankFields , calculate(distinctCOUNT([applications.areas.fields.description]), FILTER(
AP,
[applications.areas.fields.description] IN neededFieldsList
)))

[Anonymous]

Hi,

thank you! The calculation works now! I was able to create the measures for the filling rates for another area and it worked too.

But how do I add these filling rates to my matrix correctly. When I add both of them as values then they are both shown in every column, but only have values in "their correct column". How do I create my matrix now?