Average per line not total

Question

Hi all,Currently struggling with what you'd think would be a simple task!&nbsp;I am trying to look at compliance based on two separate reports:&nbsp;Report 1 - items usedReport 1 - items loggedItem 1Item 2Item 1Item 1Item 2Item 3Item 2Item 2Item 1Item 3&nbsp;I need to be able to count up all the different 'items' in report 1 &amp; divide them by all the different items in report 2 = Compliance %.&nbsp;&nbsp;There are two considerations:&nbsp;It needs an IF formula, which ensures the division is always by the smaller number from the larger number so compliance isn't report as over 100%&nbsp;I am trying to get an average compliance. Currently, the formula calculates that 5 items were sent and 5 items were logged =100% compliance and not that three 'Item 1' were sent but only one 'Item 1' logged&nbsp;I have been struggling forever with this, and scouring this forums makes it look like it hasn't been cracked yet?!&nbsp;Would appreciate help. Thank you!

dax · Answer

Hi&nbsp;Anonymous&nbsp;,&nbsp;
I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data)? Then I will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.Best Regards,Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

edhans · Answer

See if this works. The first thing I did was put this in a normal table. So your two columns of logged and used became this table in Power Query. It is simply a 1 if used/logged, or 0 if not.&nbsp;Then it was a simple matter of three measures.&nbsp;&nbsp;Item Logged Count = 
    CALCULATE(
        SUM('Item List'[Item Logged]),
        REMOVEFILTERS('Item List')
    )
-------------
Item Used Count = 
    CALCULATE(
        SUM('Item List'[Item Used]),
        REMOVEFILTERS('Item List')
    )
-------------
Compliance % = 
IF(
    [Item Logged Count] &gt; [Item Used Count],
    DIVIDE([Item Used Count],[Item Logged Count],0),
    DIVIDE([Item Logged Count],[Item Used Count],0)
)&nbsp;And this is the result:&nbsp;You can see my PBIX here to see more details of the modeling done in Power Query to get a table that made the DAX really easy.&nbsp;As an alternative, you could use one larger measure with variables:Compliance % 2 = 
VAR ItemsLoggedCount =
    CALCULATE(
        SUM('Item List'[Item Logged]),
        REMOVEFILTERS('Item List')
    )
VAR ItemsUsedCount =
    CALCULATE(
        SUM('Item List'[Item Used]),
        REMOVEFILTERS('Item List')
    )
RETURN
IF(
    ItemsLoggedCount &gt; ItemsUsedCount,
    DIVIDE(ItemsUsedCount, ItemsLoggedCount, 0),
    DIVIDE(ItemsLoggedCount, ItemsUsedCount, 0)
)

anonymous · Answer

Hi,&nbsp;Thank you for sharing, it sounded like a great plan until I went to replicate it and realised I'd forgotten to state something.&nbsp;The item names in the two reports are different&nbsp;E.g the item name in report 1 will be something like Store 1_Item 2 and in report 2 something like xycnItem1xnd. So to deal with this I created an index table and have a calculated column to standardise the name. In each report the formula strips out anything but the Item name, which in this case leaves Item 1, Item 2 and Item 3. Since it's a calculated column, I can't create a new merged report as the option - at least they way I'm doing it in query editor.&nbsp;&nbsp;Thoughts?&nbsp;

anonymous · Answer

Hi dax,&nbsp;I have done the calculation in excel to show what I would expect the result to be. Unfortunately, the PBI calculation is giving me 100% as it's calculating 5 used and 5 logged.&nbsp;&nbsp;Sum usedSum loggedComplianceItem 13133.3%Item 222100.0%Item 3020.0%&nbsp;&nbsp;&nbsp;44.4%

Forum Discussion

4 Replies

Related content