Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gianmarco
Helper IV
Helper IV

Need help with DAX formula

Dear All,

I have the following database:

gianmarco_0-1625581814275.png

 

from which I got the following table:

gianmarco_1-1625581899794.png

 

The measures involved are the followings:

1) COUNTROWS = COUNTROWS('Table')

2) DISTINCTCOUNT = DISTINCTCOUNT('Table'[RowNum])

3) QUANTITY = IF([COUNTROWS]<>[DISTINCTCOUNT],SUMX('Table','Table'[Q.ty]/[COUNTROWS]),SUMX('Table','Table'[Q.ty]))

 

The latter measure isn't working properly. If you look at the product "Kiwi" in the visualization, the quantity is "2", but it should be "1" according to the IF statement...


Is there a way to solve this? I attach a sample


Thank You

 

 

 

1 ACCEPTED SOLUTION

Hey @gianmarco ,

 

that's actually not that easy. In this case you have to create an internal table with all the conditions and then calculate the values based on that one.

The following measure should work:

Quantity NEW =
VAR vSummTable =
    ADDCOLUMNS(
        SUMMARIZE(
            'Table',
            'Table'[ProdName],
            'Table'[Q.ty],
            'Table'[RowNum]
        ),
        "@Countrows", [COUNTROWS],
        "@Distinctcount", [DISTINCTCOUNT]
    )
VAR vSummTableQty =
    ADDCOLUMNS(
        vSummTable,
        "@Quantity",
            IF( [@Countrows] <> [@Distinctcount], CALCULATE( SUM( 'Table'[Q.ty] ) ) / [@Countrows], 'Table'[Q.ty] )
    )
RETURN
    SUMX(
        vSummTableQty,
        [@Quantity]
    )

 

And I think the result is how you want it:

selimovd_0-1625587341483.png

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

 

View solution in original post

4 REPLIES 4
BA_Pete
Super User
Super User

Hi @gianmarco ,

 

I believe this is due to the fact that you've used SUMX. SUMX is an iterator function which will sum the result of each row evaluation.

 

In your example, it does the following for Kiwi:

Table Row 2: 1 (Q.ty) divide 1 (COUNTROWS on that one row that is being evaluated) = 1

Table Row 3: 1 (Q.ty) divide 1 (COUNTROWS on that one row that is being evaluated) = 1

SUMX does 1 + 1 = 2

 

Try this measure instead:

 

QUANTITY = 
IF(
    [COUNTROWS] <> [DISTINCTCOUNT],
    DIVIDE(SUM('Table'[Q.ty]), [COUNTROWS], 0),
    SUM('Table'[Q.ty])
)

 

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Dear

@amitchandak 

@BA_Pete 

@selimovd 

 

Thank you a lot for your kind answers. All three are great but still can't get what I really need.

Thanks to you I could do the following:

gianmarco_0-1625584182585.png

My problem now is the Total QUANTITY. I understand the 1,67 but I need it to be 4,00.

Now you may ask what I need to get as a result... The fact is that I have some duplication/triplication of rows in my db because some products have different expiry dates. So I need to to maintain those rows but I also need NOT to sum the quantity of those rows... 😕

 

Hey @gianmarco ,

 

that's actually not that easy. In this case you have to create an internal table with all the conditions and then calculate the values based on that one.

The following measure should work:

Quantity NEW =
VAR vSummTable =
    ADDCOLUMNS(
        SUMMARIZE(
            'Table',
            'Table'[ProdName],
            'Table'[Q.ty],
            'Table'[RowNum]
        ),
        "@Countrows", [COUNTROWS],
        "@Distinctcount", [DISTINCTCOUNT]
    )
VAR vSummTableQty =
    ADDCOLUMNS(
        vSummTable,
        "@Quantity",
            IF( [@Countrows] <> [@Distinctcount], CALCULATE( SUM( 'Table'[Q.ty] ) ) / [@Countrows], 'Table'[Q.ty] )
    )
RETURN
    SUMX(
        vSummTableQty,
        [@Quantity]
    )

 

And I think the result is how you want it:

selimovd_0-1625587341483.png

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

 

selimovd
Super User
Super User

Hey @gianmarco ,

 

acutally the measure is working properly. I just think you want something else.

SUMX is an iterator, what means it goes through the table line by line, calculates the expression for each line and at the end is building a SUM of the expressions for each line.

For the kiwi, it takes the first line and calculates [Q.ty] (what is 1) divided by [COUNTROWS] (what is 1). So the result is 1.

Then it will do the same for the next row, the result is again 1.

Afterwards SUMX will sum up the two results and as 1 + 1 = 2 you get the correct result.

 

I think what you want is:

SUM( 'Table'[Q.ty]) / [COUNTROWS]

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.