Groupby with different calculations

[Wedding55]

Hello all,

i have a table with following measures:

digital: count of digital products per customer

offline: count of offline products per customer

total: count of total products per customer

Then i created a new column "typ" with the follwing expression:

type = SWITCH(TRUE(),

[count of digital products] = [count of total products], "online",

[count of offline products] = [count of total products], "offline",

"mix")

 

but the problem is, that when i select the column "type" in the visual that for the customers with type "online" and "offline" i see two rows instead of one row with the type "mix". (see screenshot). 

For your information you can see the measures used for the digital, offline and total column:

digital= CALCULATE(DISTINCTCOUNT('Cases'[casenumber]), 'Cases'[organisation]="Org1", 'Cases'[tc]=1)

total = CALCULATE(DISTINCTCOUNT('Cases'[casenumber]), 'Cases'[tc]=1)

 offline = total - digital

Can anyone help me with the problem?

Best Regards

Henning

[Wedding55]

@v-yanjiang-msft  Thanks for your help. Its works fine 🙂

[v-yanjiang-msft · Accepted solution]

Hi @Wedding55 ,

According to your description, I create a sample.

And create measures like yours, finally get the correct result.

May I know before you put "type" in the visual, is there a single row or two rows of customer 100204? If there're two rows, I think it's not the problem of "type".

Or you can try to modify the formula like this:

digital =
COUNTROWS (
    FILTER (
        ALL ( 'Cases' ),
        'Cases'[customer] = MAX ( 'Cases'[customer] )
            && 'Cases'[organisation] = "Org1"
            && 'Cases'[tc] = 1
    )
)

total =
COUNTROWS (
    FILTER (
        ALL ( 'Cases' ),
        'Cases'[customer] = MAX ( 'Cases'[customer] )
            && 'Cases'[tc] = 1
    )
)

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[Wedding55]

Maybe this explanation helps:

I have 1 table:

casenumber	customer	type
1	100084	online
2	100075	offline
3	100084	offline
4	100085	online
5	100085	online

the result should look like this:

customer	type
100075	offline
100084	mix
100085	online

The special thing is to get the type "mix" because its not existing in the original table but if i have the same customer several times with different types i want in the result table only one row for this customer with the type "mix".

Thanks in advance 🙂

[amitchandak]

@Wedding55 , if these are measure then the below need to be a measure [count of digital products] , [count of total products],

type = SWITCH(TRUE(),
[count of digital products] = [count of total products], "online",
[count of offline products] = [count of total products], "offline",
"mix")

if you need this slicer or axis- follow the approach in this video

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://www.youtube.com/watch?v=W4EF1f_k6iY

[Wedding55]

Hello amitchandak,

yes these are measures but my problem is the aggregation.

When i choose the column "type" and i have a customer with digital and offline cases then powerbi shows me two rows for one customer with type "online" and "offline". But in this cases i want one row with the type "mix". Do you understand my problem or should i explain it more detailed?

Best Regards