Count IF / 'Set Analysis'

[MikeGaunt]

Hi All, 

Just started a new job where they use Power BI, having used Qlik in the past I am having trouble with getting the correct outputs. 

If I consider the table below 

ideally I need the counts for the coulmn to be summed and then the count of 1 to be divided by the total per month 

as the below (Which works for one month but not when I add additional months data in)

as we can see 118/175 = 67.43%

hope this makes sense

Thanks in advance I am learning 

@countif

@IF

[v-yalanwu-msft]

Hi, @MikeGaunt ;

You could modify it.

Measure = DIVIDE( COUNT([count]),CALCULATE(COUNT([count]),FILTER(ALL('Table'),[Month]=MAX([Month])&&[Vendor]=MAX([Vendor]))))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @MikeGaunt ;

If you could  post sample data as text and expected output,It makes it easier to give you a solution.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MikeGaunt]

@v-yalanwu-msft 

I will do my best. 

so the raw data will come across like this 

Vendor

Name 1

Purchasing Document

Material

Material description

Purchasing Group

Month

Material Doc. Year

Corr. Qty. Reliab.

Correction On-time del

Sched. date

GR date

Schedule line number

Post.qty.

Quantity Received

Scheduled Quantity

Days to late WE

Days to early WE

Delivery Note

Changed by

Changed On

Dat.reliab.

and effectively for each Month and Vender I need to track the percentages of the 100. 

as below 

and then graphically represent it. 

Thanks for all the support.

[v-yalanwu-msft]

Hi, @MikeGaunt ;

You could create a measure as follows:

Measure = 
DIVIDE( SUM([Count of C olumn]) ,CALCULATE(SUM([Count of C olumn]),ALLEXCEPT('Table','Table'[Month])))

Or

Measure = 
DIVIDE( SUM([Count of C olumn]) ,CALCULATE(SUM([Count of C olumn]),FILTER(ALLSELECTED('Table'),[Month]=MAX([Month]))))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MikeGaunt]

@v-yalanwu-msft This works great Thank you, 

Per chance are you able to guide me on replicating this in a graph. 

So the vendor would act as the Legend, the month as the x axis and the Measure as the value  BUT only for the the 100 percentages. 

so plot Month 1, Vendor VO640  = 69.15% to be plotted on a graph. 

I have tried to do this but cannot seem to get the right output. 

Thanks in advance. 

[v-yalanwu-msft]

Hi，@MikeGaunt ;

Why don't Vendor set Count of Column as Legend? If Vendor is set as Legend, logically it must be 100%, so you can try to set Count of Column as Legend.

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MikeGaunt]

@v-yalanwu-msft 

Thanks for the input, however I believe my issue is that the "Count of the column " is already a generated field it is not part of the intial load.

Column = count(Sheet1[Correction On-time del])

Whereas I see in your example it is loaded as a field. 

My other issue that will come into it is that there will be multiple vendors within the data set, currently I am working with a smaller dataset in order to learn and practice. 

script is below 

= Table.TransformColumnTypes(#"Promoted Headers",{{"Vendor", type text}, {"Name 1", type text}, {"Purchasing Document", Int64.Type}, {"Material", type text}, {"Material description", type text}, {"Purchasing Group", type text}, {"Month", Int64.Type}, {"Material Doc. Year", Int64.Type}, {"Corr. Qty. Reliab.", Int64.Type}, {"Correction On-time del", Int64.Type}, {"Sched. date", type date}, {"GR date", type date}, {"Schedule line number", Int64.Type}, {"Post.qty.", Int64.Type}, {"Quantity Received", Int64.Type}, {"Scheduled Quantity", Int64.Type}, {"Days to late WE", Int64.Type}, {"Days to early WE", Int64.Type}, {"Delivery Note", Int64.Type}, {"Changed by", type text}, {"Changed On", type date}, {"Dat.reliab.", Int64.Type}})

[MikeGaunt]

Hi @amitchandak 

In the top table the total is across all months whereas it needs to be each each month as in the second table. 

[amitchandak]

@MikeGaunt , what is wrong and what is expected output

% of grand total when [Count of column] is a measure

divide( [Count of column], calculate( [Count of column], allselected())  )

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.