Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Experts,
I have a question that struggle me for a time, would anyone can help me to solve?
I would like to create 4 measures (It's 2 types of measure in fact)
Please find the raw sample file for download
https://gbil-my.sharepoint.com/:u:/p/simon_chung/EeDrG4mY275IsXZCxPWAPsIBAVhcBqgydIfn0tkGvUTKOQ
There is a CODE contains A, B and the others, in this case we will only focus on A & B.
Customers will be classified into
A only - Yellow
B only - Orange
Mixed: A - Green
Mixed: B - Blue
The expected result is to create 4 Measures to get the sum of A only, B only, Mixed: A, Mixed: B
Note the results will vary with the time of selection, so it is no way to create Column to solve.
Expected result:
The question looks simple, but it struggle me for a time really.
Many many thanks
Simon Chung
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi,
Your expected result seems incorrect. On creating a simple Pivot Table (see image below), there is no Customer (for any Item) which has both A and B. Once you correct your source data, these measures should works for "Only A" and "Only B"
Only A = SUMX(FILTER(SUMMARIZE('Table','Table'[Customer],"ABCD",CALCULATE(DISTINCTCOUNT('Table'[code]),all('Table'[Item])),"EFGH",CALCULATE([Total],'Table'[Code]="A")),[ABCD]=1),[EFGH])
Only B = SUMX(FILTER(SUMMARIZE('Table','Table'[Customer],"ABCD",CALCULATE(DISTINCTCOUNT('Table'[code]),all('Table'[Item])),"EFGH",CALCULATE([Total],'Table'[Code]="B")),[ABCD]=1),[EFGH])
Hi @Ashish_Mathur
I would like to make this question simple, I now just want the Yellow part - A Only.
such that the sum of amount will exclude those customers have both A and B. hope that you can help, many thanks!
Simon
Hi,
You may download my PBI file from here.
Hope this helps.
You are welcome.
Sorry, need your help again.
There is something strange in the real data, I'm not sure if the no. of record too big.
I found that the slicer cannot take control.
For example. the amount of SGBM0000054 (204) should go to "A only" (A = "01", B = "02")
as there is no amount for "02"
but now it go to Mixed A. do you know what's the problem and how to fix it?
thanks a lot
Simon
Hi,
Share the link from where i can download your PBI file.
Hi,
Those answers seem correct to me. The 3 items that you see in Table2 : B-B-VFSM, B-B-VFSPM and B-S-PBS(S) have codes other than 1 and 2 (see the 3 screenshots below). In the [A only] measure, we have excluded "Other". There is no Other entry in the Code column in your dataset. I guess you want to exlude all except codes 01 and 02. If that indeed is the case, then the formula will have to be modified.
Oh So sorry for my careless mistake
Thanks so much
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
116 | |
104 | |
77 | |
71 | |
51 |
User | Count |
---|---|
146 | |
107 | |
106 | |
89 | |
65 |