Forum Discussion
Count IF / 'Set Analysis'
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
9 Replies
Hi, Anonymous ;
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.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.
Hi，Anonymous ;
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.
Anonymous , 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.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.
Hi, Anonymous ;
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.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.
Hi, Anonymous ;
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.
