Cannot use a Measure properly in slicer + Calculation

[Jaweed]

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

[Jaweed]

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

[Anonymous]

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.

[Jaweed]

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.

[Anonymous]

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.

[Jaweed]

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

[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. 

[Anonymous · Accepted 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.

[Jaweed]

Hi @Anonymous 

many many thanks. It works perfectly. I highly appreciate your help.