Forum Discussion
COUNTA and SUM for different periods
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!
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 measureSupplierCountTerms =
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
3 Replies
- mahoneypat
Microsoft Employee
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 measureSupplierCountTerms =
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