Forum Discussion

gbarr12345's avatar
gbarr12345
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

MacBook Sold in April measure

Hi everyone,

 

I'm looking to create a measure to put onto a card visualisation based on the number of Mac Books Sold in April.

 

I created the measure and got no error but when I pull it into a card the number that appears is incorrect.

 

Any idea why this number is a lot higher than it should be?

 

I have the code below and PBIX attached.

 

MacBook Sold April =
VAR _Item = SELECTEDVALUE( 'Sales Table'[Item Code])
VAR _Period = SELECTEDVALUE( 'Sales Table'[Period])
VAR Sales_Period =
    VALUES( 'Sales Table'[Period] )
VAR VirtualTable =
    FILTER(
        SUMMARIZE(
            'Sales Table' ,
            'Sales Table'[Period] ,
            'Sales Table'[Item Code] ,
            'Dimension_Item Table'[Item Description]
        ),

        'Sales Table'[Item Code] = 7
        || 'Sales Table'[Period] = 202404
    )
    RETURN
    COUNTROWS(VirtualTable)

 

PBIX - https://drive.google.com/file/d/1pRRLgjcz7Ooo-yXndknfLtYqryr0lOR3/view?usp=drive_link

 

 

 

Need Help
  • nandic's avatar
    nandic
    2 years ago

    gbarr12345 you could try this formula:

    CALCULATE(COUNTROWS(FILTER('Sales Table','Sales Table'[Item Code]=7 && 'Sales Table'[Period]=202404)))

    It returns 3 as there are 3 transactions.
    In your formula there are 2 issues: it is checking for all rows that are either MacBook Pro OR period 202204. We need AND condition instead of OR.
    Another issue is that you grouped based on period/item in virutal table, so it will group by Macbook Pro 202204, returning only 1 row count.
    If you used summarize, you would need to add another group by transaction to get count of transactions.

    Cheers,
    Nemanja Andic

2 Replies

  • nandic's avatar
    nandic
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    gbarr12345 you could try this formula:

    CALCULATE(COUNTROWS(FILTER('Sales Table','Sales Table'[Item Code]=7 && 'Sales Table'[Period]=202404)))

    It returns 3 as there are 3 transactions.
    In your formula there are 2 issues: it is checking for all rows that are either MacBook Pro OR period 202204. We need AND condition instead of OR.
    Another issue is that you grouped based on period/item in virutal table, so it will group by Macbook Pro 202204, returning only 1 row count.
    If you used summarize, you would need to add another group by transaction to get count of transactions.

    Cheers,
    Nemanja Andic