Single product on document

Hi Anonymous ,

I've gone about this a different way from before due to the extra flexibility you need around selecting different items. As you need this to be dynamic, I've done it in DAX instead of Power Query.

I've called your example data 'factSales', which you will see referenced below, which you can replicate with the following PQ code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zVjNbttGEH6VhYDeVIc/oiwdV+Ra2pLcZXbJMmQaGHYNBEUCJ0iQS25G/Qa5FH2VHGO9V2eWS8qm5J+EbNGDLZrWfJyfb2a+5cuXk4yqZ3Pf9YLJdKIzXs58uIBPSSKmM6ZoJDXRdW7vwocLP958upy8mu7Mj+Hm63dnb98sPbiiYCgTojPF83oKNyLFRcytse/fNXaDIcbNky/OLtHxnK45zVmSMFITLTVLSUjVSgqqKEZAdV4RTlIaUyVFZTFn7l1MJxgf0/PHx9zFPjPVo+sNy3NOSoTkjGRM5xIeoIpYJjfXh4Ex5Yvj6eKOs54BvnztLrAgKVM8pEKC2faKnsItQTP5CyNrqW6uQ2bdc+cH3FtL/B0riDCTKY+kkmWFPvFQ5rlxqi6yNkKvV13PMY6cf/qAHoUbxjRbFWrNFBKjueqyc9x7vP/jts6iTcD8TgJqkiYs5hDMI6no4y0N3ltDdR2ruorY9iqmABgXKqyhHERQInWuGkKQ1eo51kvVLL651jv6e/8RsNvMAQ+ZtWFcsJgJ4kwDkpAVC+uY9gZCMKq57wwy94Y93U6zofR3DcrnS6SfljEpq5DrmIoKOwEaffsn1IfMHAdI1XyFQ2vqDY1bSG/Wc8y1jpnIdJVRqC3VmgIfWcLE+ql8dJd23LtOO+4zWiRUMEVWRX70+MQfASEYjIA3Lz6+MQ1+oqocEug5DmYhkhHFP6El5IrL8t5ENJPufdC6sC64EExrSxQSOI6pzi037unBQRjuYgQ/TEJFzd0APSohBWRN62cCfgH7/cRm1DKk5qno0uL0iNa4Ayv5GIuz5gL8YCcMdpYCxhpPaBLLjYQNlsrQAGEq5/7dTeIj+vnl2fvf56fujrM1ZwksO6KZCDdA4STf0EKbbzydv7D53l0YT1P+gmhszpyYwrP4FgVKClOx3IV6aNCNLSDmzeo5MxyHiLngiFdXAoYuNjnDhZjyVJ4KVuKChKxk3TD2euF6jSB5P+/YoXi6YrDBRMsP39/jx95Mnv1/UOwke/vuAi9AagKvEhmBNvjtk+OYAWxuVs/KSh2FBexcJXhYd5OxLxRGxrMzKcAot1+rkrOQ8IxiG923RHoc8N2WV9gX2yup5fZvSBb9NaEvOnKlha5/TrmMpniJbNuomorJgQ09Wx5YcqbdZ9gNGcs0B/+8B7rcO7BlTZfPEav89iWC6m2/yu3VzXUUy5Le2+smYu+AQ0ZwIV0bgUVOFBVh0UTR01zLg/vyh82Xg8yt5gfz+c48k8n3Pfp4Z4sMjGX9hCe3aqNRK2pFc0pAGShhxEGa3K84evKgU634Cac3BfsDznEyy7gk8weR+pvVDsUPf2Ba1vI0lDqVkA6eG3I+eCRzuvOc2xivEimjCoamycEdW6+fStt3/l7fPVl5YjEGioIRIPzBEN7cVhP/mdA8Z48J0P6AWA4EMAe488+f0BEl9bcv26ujut19MKt8q0nsAQ5DnfVPkk47pExJmyGVwgCEQfWQHjkksYbDuME43iwsjGfoito+B0WPBQYpTxY/3QPTE1lG89UxSwzK84JpGvEkoaSEASxLLpDpEYgHCkrh5i/dAe3Nn8ACmWwLGm7gnPcYSNBnCxbbyPHZwnYYaTsw0zXDbSnYrWbcl+eHxtGoiK1K/vDZpCyHSalsoBUe6vd3Zl8mzDoCYM5yCT4QHW5KlmVMm+35HTzwFmOiOc3rEhMZdBTsALYqElzCNcgGEAVx/23J3tR2hiJ4s250m3VsJYHOZRgT3Cc5XzMjCo4mT1UF/wLkYjL4xVIwGMLubSDjskEpGY71GrY+HMUjGKxHBynZ31fBeDAjHv7GgXIbhfnRrMPmhBYjLUHd2IOPvdmF1X/FGQwFaLcohtJpIv9BMWTo9uof", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DokumentTow = _t, IndeksTow = _t, Nazwa = _t, GrupaAdm = _t, Ilosc = _t, Sprzedaz = _t]),
    repCommaDecimal = Table.ReplaceValue(Source,",",".",Replacer.ReplaceText,{"Sprzedaz"}),
    chgTypes = Table.TransformColumnTypes(repCommaDecimal,{{"DokumentTow", type text}, {"IndeksTow", type text}, {"Nazwa", type text}, {"GrupaAdm", type text}, {"Ilosc", Int64.Type}, {"Sprzedaz", type number}})
in
    chgTypes

You can delete the 'repCommaDecimal' step to return to your regional number formats.

1) I set up a disconnected product table (nazwaDisc) in Power Query, like this:

let
    Source = Table.Distinct(Table.SelectColumns(factSales, "Nazwa"))
in
    Source

2) I sent both tables to the data model, ensuring that is NO relationship between them (PBI might do this automatically when loaded, so make sure to delete if it does).

3) I created a basic measure to count items sold:

_noofItemsSold = SUM(factSales[Ilosc])

Then I created the following measure that will count all items on all transactions that contain the item selected from the disconnected product table:

_noofItemsSoldWithSelection = 
CALCULATE(
      [_noofItemsSold],
      CALCULATETABLE(
          VALUES(factSales[DokumentTow]),
          INTERSECT(
             ALL(factSales[Nazwa]), 
             VALUES(NazwaDisc[Nazwa])
          )
      )
  )

This gives me the following output:

I've attached my PBIX below so you can have a play with it and see how it's all set up.

Pete