Forum Discussion

rkgundabhat's avatar
rkgundabhat
Helper I
2 years ago
Solved

Percent Measure

Hello, I am working with a table with columns Item, _and sales. Somes of the items has 0 sales. I need a measure which I use to show in a data card which shows the percent of items with 0 sales for ...
Need Help
  • v-zhangti's avatar
    v-zhangti
    2 years ago

    Hi, rkgundabhat 

     

    You can try the following methods.

    Measure = 
Var _N1=CALCULATE(COUNT('Table'[Item]),FILTER(ALLEXCEPT('Table','Table'[Department]),[Sales]=0))+0
Var _N2=CALCULATE(COUNT('Table'[Item]),ALLEXCEPT('Table','Table'[Department]))+0
Return
DIVIDE(_N1,_N2)

    Is this the result you expect? Please see the attached document.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

rkgundabhat's avatar
rkgundabhat
Helper I
2 years ago

v-zhangti 

I apologize for the delay. Here is a sample data. Items are unique. What I want to do is to find out for any given department  what is percentage of items with 0 sales

Department ItemSales
Dept1P14
Dept1P25
Dept2P30
Dept2P44
Dept3P55
Dept3P60
Dept4P40
gmsamborn's avatar
gmsamborn
Super User
2 years ago

Hi rkgundabhat 

Would something like this help?

Pct of Items with No Sales = 
VAR _TotalItems = DISTINCTCOUNT( 'Sales'[Item] )
VAR _ItemsWithNoSales =
    COUNTROWS(
        FILTER(
            SUMMARIZE(
                'Sales',
                [Item],
                "_Sales",
                    SUM( 'Sales'[Sales] )
            ),
            [_Sales] < .00001
        )
    )
VAR _Pct =
    DIVIDE(
        _ItemsWithNoSales,
        _TotalItems
    )
RETURN
    _Pct

Let me know if you have any questions.

 

Percent Inactive Products.pbix