Measure Total Problem

[yugofukuda]

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.

[v-kkf-msft · Accepted solution]

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.

[mahoneypat]

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

[VijayP]

@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]

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? 

 

[m3tr01d]

@yugofukuda What does the Month Diff column means in your data?

[yugofukuda]

Hi, Thank you for your help!!

Month diff means the difference between "Sales date" and "Service date".

For example, # Sales product is 69 in July and # Service to customers is 2 in July, the number in 0 month diff is 2.
and if we provide 3 services in August to customers of July who bought our products in July, the number in 1 month diff is 3 because it already passed 1 month after selling products (=1 month diff).

[v-kkf-msft · Accepted solution]

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.

[yugofukuda]

Hi, I would really apreciate if you could help me.
I´m cheking your pbix file and I found that if there is one service in August, it generates a difference between 5 and 6 in total. How can I solve this problem? I wanted to insert pbix file but it couldn´t do that...

Service = COALESCE(CALCULATE(count('Table'[Service date]),FILTER(ALL('Table'[Month dif]),'Table'[Month dif]<=MAX('Table'[Month dif]))),0)

[v-kkf-msft · Accepted solution]

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

[yugofukuda]

Sorry for my late reply, thank you very much!!

[yugofukuda]

Sorry for my late reply. thank you very much...!!!!

it works well!!!!!!!!!!!!!