Forum Discussion

yugofukuda's avatar
yugofukuda
Helper IV
5 years ago
Solved

Measure Total Problem

Hi,  I have a problem in the total measure. 

I would like to calculate % retention of clients after selling products as below. for example, regarding 69 products sold in July, we provided services to 5 clients then % retetion until today is 7% (5÷ 69)  in Total, but the number in Power Bi is 5% (5 ÷ 109) consideraing 40 products sold in August.

 

I´m using this formula "CALCULATE(sum(Table[Count]),ALLSELECTED('Table'[Month dif])))"  for calculating total but I can´t calculate well.  I would appriciate if you could help me. 

Thanks.

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    Hi yugofukuda ,

     

    Try the following formula:

     

    Sales = 
CALCULATE(
    sum('Table'[Count]),
    ALLEXCEPT('Table','Table'[Month])
)
    Sales Total = 
IF(
    NOT(ISFILTERED('Table'[Month]))
        && HASONEVALUE('Table'[Month dif]),
    SUMX(
        FILTER(
            ALLSELECTED('Table'[Month],'Table'[Month dif]),
            'Table'[Month dif] = MAX('Table'[Month dif])
        ),
        [Sales]
    ),
    [Sales]
)
    Service/SalesTotal = [Service] / [Sales Total]

     

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

    Best Regards,
    Winniz

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

     

  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi yugofukuda ,

     

    Please try the following formula:

     

    Service = 
var _count = 
CALCULATE (
    COUNT ( 'Table'[Service date] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Month] = MAX ( 'Table'[Month] )
            && 'Table'[Month dif] <= MAX ( 'Table'[Month dif] )
    )
)
var tab =
SUMMARIZE (
    'Table',
    'Table'[Month],
    'Table'[Month dif],
    "total", _count
)
return 
IF (
    ISFILTERED ( 'Table'[Month] ),
    _count,
    IF ( 
        NOT( ISFILTERED ( 'Table'[Month] ) ) && ISFILTERED ( 'Table'[Month dif] ),
        SUMX ( tab, [total] ),
        COUNT ( 'Table'[Service date] )
    )
)

     

    Best Regards,
    Winniz

     

     

10 Replies

  • VijayP's avatar
    VijayP
    Community Champion
    5 years ago

    yugofukuda 

    ALLSELECTED is only for controlling the data from External Filters (like Slicer) but in your scenario it doesnt work!

    Just use sum(columnname) and distribute in the Matrix and you may get right result!

    • yugofukuda's avatar
      yugofukuda
      Helper IV
      5 years ago

      Thank you very much for your reply!! but it dosn't work using Sum function. 
      If I use sum, the result is as below (3 means that we provided sevices to 3 custmers of 69 clitens bought our products in August). Could you please give me another solution? 

       

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Do you get the expected result if you just do a simple SUM

     

    = SUM(Table[Count])

     

    and for your % try = DIVIDE(SUM(Table[Service]), SUM(Table[Count]))

     

    Pat