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.
I have a table, simplified for this post, as Company, and amount due. Let us say 3 companies, with total records 7. I have a visual which shows the Total Due for each company, I need to segment the companies( Clients). I created a measure M, where by if the Total Due is 0-500, M = 0, if Total Due is 501 -1000, M = 1 and if Total Due > 1000, M =2. This is working correctly.
I am faced with 2 problems.
1: Since it is a measure ( my belief) I cannot make a slicer with M. I would like the category 0,1, 2 to be mapped as Low, Medium and High
2: What will be the DAX to get the percentage of each category, In the case below, I would like to show Category 0 ( Low) with value 1400/ (1400+800+300) = 56%; Category 1 = 800/(1400+800+300) = 32% and finally category 2 as 300/2500 = 12%, ( I tried Calculate, but I get an error, I guess I am not doing it correctly ) Your assistance is highly appreciated. Thanks.
CPY A 300
CPY A 500
CPY A 600
CPY B 700
CPY B 100
CPY C 200
CPY C 100
I have a table visual as
CPYA 1400
CPY B 800
CPY C 300
Solved! Go to Solution.
Hi @Jaweed ,
Here's a link to the pbix:
https://1drv.ms/u/s!AikPceQOhqFEhBSvEY8jIEN-azfZ?e=IzzVmK
Step 1: Create a measure for Company Count:
Company Count = DISTINCTCOUNT( Data[Company] )
Step 2: Create a measure for Company Count by Category:
Company Count by Category = CALCULATE([Company Count], FILTER(SUMMARIZE( Data, Data[Company], "Amount Due", [Total Amount Due] ), COUNTROWS( FILTER('Company Category', [Total Amount Due] >= 'Company Category'[Min] && [Total Amount Due] <= 'Company Category'[Max] )) > 0 ))
Step 3: Create below 3 measures:
High Company Count = CALCULATE( 'Company Category'[Company Count by Category], FILTER( 'Company Category', 'Company Category'[Category] = "High" )) Medium Company Count = CALCULATE( 'Company Category'[Company Count by Category], FILTER( 'Company Category', 'Company Category'[Category] = "Medium" )) Low Company Count = CALCULATE( 'Company Category'[Company Count by Category], FILTER( 'Company Category', 'Company Category'[Category] = "Low" ))
Step 4: Then you can drag these measures in the respective cards as below:
Thanks.
The solution proposed so far works perfectly. I managed to adapt it to our problem. Let us say the categorisation is working as below
Company Category Amount Due
Cpy A High 15000
Cpy B. Medium. 8000
Cpy C. High. 1600
Companies are in table T1.
Now I would like to have another column Amount paid which is in table T1 also.T1 and T2 are linked by ProformaNr. T1 has 1 to many relation with T2.
Table T1. Table 2
company code. ProformaNr
ProformaNr. LineNr
Amount paid. AmountDue
Thanks
Hi @Jaweed ,
Could you please provide some sample data from both the tables. Also can you share how the Amount Paid column is generatede (is it coming from source or we need to derive it in PowerBI ). It would be great if could share the expected output.
Thanks.
hi @Anonymous
InvoiceHeader Table
Cpycode ProformaNr AmountPaid
CPYA 5 12000
CPYB 6 600
CPYC 7 0
CPYA 8 200
CPYA 9 0
CPY D 10 400
InvoiceItems Table
ProformaNr AmountDue ServiceCode
5 1000 A
5 3000 B
5 8000 C
6 400 A
6 200 C
7 1000 A
7 2000 B
7 10000 C
8 300 A
8 100 B
9 1000 A
10 400 A
Service code is just used for some filtering. Working properly.
Output
Company AmountPaid AmountDue Category
CPYA 12200 13400 High
CPYB 600 600 Medium
CPYC 0 13000 Low
CPYD 400 400 Low
InvoiceHeader and InvoiceItems are linked by ProformaNr. We assume category is based on a range in AmountPaid ( working properly as per previous post). AmountDue is working properly ( as per your previous post). I cannot get the Amountpaid to work properly. Thanks for your usual help.
Hi,
Here's a link to pbix for your requirement.
https://1drv.ms/u/s!AikPceQOhqFEhA5GA8ycUuE4-iYP?e=68wAgUs
Please accept this as solution if it satisfies the need.
Thanks.
Thank you very much. I tried to adapt the solution these past days to my problem. I believe this is advance DAX. It did work, I am grateful. Can you please guide me how to get displayed on cards the no of companies for each category high, low, medium?
I am struggling with it.
many thanks
jaweed
Hi
I have table A, with data on a PBI table visual. working ptrpoerly.
I have a second table B, with other records and I need to have same result set, based on the same filtering as applied on Table A. The filtering is working properly. I attach some simplified tables below. I appreciate some help from members.
Table A | |
Entity | Amount |
A | 110 |
B | 150 |
C | 30 |
D | 60 |
E | 100 |
Rows and columns of Table B are below, coming from a query and dynamic. We cannot preset countries.
Table B | ||
entity | Shareholder | Country |
A | John | UK |
A | Bob | UK |
A | Paul | France |
B | Liz | India |
C | Ellie | Germany |
D | Jack | China |
D | Alex | France |
E | Lucy | UK |
E | Diane | Germany |
E | Suzy | India |
E | Bob | India |
E | Anne | Botswana |
I applied a filter ( Amount in range between Amount 100 and 150). Visual below, working properly with output as below.
Visual 1 Entities | |
Entity | Amount |
A | 110 |
B | 150 |
E | 100 |
Now I want the same records table B to appear as in the format below ( I tried matrix, but I am not so sure). The row value is just the count. Note that we do not have any country China appearing, as it pertains to Entity D, which does not fit in the filtering.
Desired output.
Entity | UK | France | India | Germany | Botswana |
A | 2 | 1 | |||
B | 1 | ||||
E | 1 | 2 | 1 | 1 |
Many thanks, members. I have given much try before resorting to your assistance.
Hi @Jaweed ,
Here's a link to the pbix:
https://1drv.ms/u/s!AikPceQOhqFEhBSvEY8jIEN-azfZ?e=IzzVmK
Step 1: Create a measure for Company Count:
Company Count = DISTINCTCOUNT( Data[Company] )
Step 2: Create a measure for Company Count by Category:
Company Count by Category = CALCULATE([Company Count], FILTER(SUMMARIZE( Data, Data[Company], "Amount Due", [Total Amount Due] ), COUNTROWS( FILTER('Company Category', [Total Amount Due] >= 'Company Category'[Min] && [Total Amount Due] <= 'Company Category'[Max] )) > 0 ))
Step 3: Create below 3 measures:
High Company Count = CALCULATE( 'Company Category'[Company Count by Category], FILTER( 'Company Category', 'Company Category'[Category] = "High" )) Medium Company Count = CALCULATE( 'Company Category'[Company Count by Category], FILTER( 'Company Category', 'Company Category'[Category] = "Medium" )) Low Company Count = CALCULATE( 'Company Category'[Company Count by Category], FILTER( 'Company Category', 'Company Category'[Category] = "Low" ))
Step 4: Then you can drag these measures in the respective cards as below:
Thanks.
Hi @Anonymous
many many thanks. It works perfectly. I highly appreciate your help.
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |