COUNTA and SUM for different periods

[Anonymous]

Hi All!

It's me again.. lol

This time I'm trying to build a report to show how many different payment terms we have by supplier at each period.

My database looks like this:

As of	WeekNum	Supplier	Payment term
10.aug.20	33	A	30 days
10.aug.20	33	C	60 days
10.aug.20	33	Y	30 days
10.aug.20	33	A	90 days
10.aug.20	33	Y	75 days
10.aug.20	33	Z	120 days
10.aug.20	33	Y	45 days
17.aug.20	34	A	60 days
17.aug.20	34	B	30 days
17.aug.20	34	T	90 days
17.aug.20	34	Y	75 days
17.aug.20	34	U	120 days
17.aug.20	34	B	45 days
17.aug.20	34	Y	60 days
17.aug.20	34	U	30 days

The output i'm looking for is:

Number of payment terms	Number of suppliers
Week 33
1	2
2	1
3	1
Week 34
1	2
2	3

I've been trying to do that through a New Column and Measure.. but failed on both!

Absolutely no clue about this one..Can you help me with this one additional question?? 😁

Thanks again for the support!

[mahoneypat · Accepted solution]

This one is a little trickier.  To do it, you need to first make a disconnected table to hold the possible values for number of terms with an expression like this.  On the modelling tab, click on New Table and enter the expression below.

NumberOfTerms = GENERATESERIES(1,5,1)

Once you have that table (no relationship needed to the other tables), make this measure

SupplierCountTerms =
VAR numterms =
SELECTEDVALUE ( NumberOfTerms[Value] )
RETURN
COUNTROWS (
FILTER (
VALUES ( Terms[Supplier] ),
CALCULATE ( DISTINCTCOUNT ( Terms[Payment term] ) ) = numterms
)
)

Then make a matrix visual with the WeekNum column and the NumberOfTerms[Value] column on rows and the measure above on values to get this.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Awesome!! worked perfectly.

Thanks again for the support.

[Anonymous]

@mahoneypat 

Hello Pat! I was wondering if you could help me with this one too? 😁

Thanks!