Forum Discussion

Laufer_Israel's avatar
Laufer_Israel
Icon for Helper I rankHelper I
6 years ago

Returns only first row with lookupvalue

Hi all, I have a table as can be seen below and I would like to see only the first row for "Total sales 2018" column. It is important to note that "Total sales 2018" is a data which has been taken ...
Need Help
Laufer_Israel's avatar
Laufer_Israel
Icon for Helper I rankHelper I
6 years ago

Guys,

As I mentioned, the table I shared with you is only an example for my data. (it is not even close to how my data looks like...)

Unfortunatlly, due to confidential matter I can't share my data with you.

Back to your proposals, it isn't solve my issue..

v-xicai - your proposal dosen't solve it since the rank column returns totally diffrent figures, so the "1" in the second DAX is not relevant.

Jimmy801 - I'm totally confuse from your proposal. my Advances Editor shows diffrence figures since as I noted above, my data looks diffrence in comparison to my exaple..

Anyway, many thanks for your willing to assist with this frustrating issue.

 

Other thoughts??

Jimmy801's avatar
Jimmy801
Icon for Community Champion rankCommunity Champion
6 years ago

Hello Laufer_Israel 

 

just copy my entire code into a NEW BLANK query and check out how my solution works.

 

Jimmy

  • Laufer_Israel's avatar
    Laufer_Israel
    Icon for Helper I rankHelper I
    6 years ago

    Thank you Jimmy801.

    I forgot to indicate that I have also a column with dates in my data.

    Actually what I am trying to do is to visualize the total sales for 2018 with a dependency of a slicer.

    Means: If I will choose on the slicer a period between Jan to Apr (for an example) ... I want to see only the sales of those month.

    Currently it can't be possible since the SUM function taking the multiple sales (i.e: if customer apear in 4 rows it summarize his sales).

    Any other suggestion? maybe thare is some DAX that can solve it??

    Many thanks in advance,

    Israel

    • Jimmy801's avatar
      Jimmy801
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Hello Laufer_Israel 

       

      as you can see in my solution, the new column shows the sales only once. So assuming for every row, where the same amount is stated, also the month is applied and therefore you can sum this column.

      Question... why does in the column total Sales show up the value on every row in first place. This can be never the real data, can it?

       

      Jimmy

      • Laufer_Israel's avatar
        Laufer_Israel
        Icon for Helper I rankHelper I
        6 years ago

        The "Total Sales" column is the total sales for 2018 per customer which has been taken from another table (with LOOKUPVALUE).

        My original table contains only sales per customer per specific product. Now, I would like to add to this table the total sales for a specific year, per customer, and this is the source of my issue... (that lookupvalue DAX returns multiple figures).