Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear all,
I have a table like below:
Invoice No Product Code Value
50 101 200
50 103 550
50 104 300
51 103 500
51 105 600
52 102 200
53 103 400
54 101 650
54 102 300
54 103 200
55 102 1200
55 103 1000
Here, Total value of invoice no 50 = 1050 (200+550+300)
51 = 1100
52 = 200
53 = 400
54 = 1150
55 = 2200
I want to count the number of distinct invoices with total bill value between 1000 - 2000 and containing product 101 or 102 or both.
Here the total number of invoices with bill value between 1000- 2000 is 3 (i.e. 50, 51 and 54)
From this, two bills contain product 101 or 102 or both. (i.e. 50 and 54)
Therefore the number of invoices with total bill value between 1000-2000 and containing product 101 or 102 or both is 2.
How can I get this number? Please help. Thank you.
Solved! Go to Solution.
@Anonymous from what you can see the measure does actually work however it expects some sort of context,
for example if you place it on a matrix you would see that the value woudl be summed by invoice number and measure test would show 1 and 1 for invoice 50 and 54
so to use it on a card, i think you actually need to summarise this in a table
try this
Table Test =
SUMMARIZE (
Table1,
Table1[Invoice No],
"Measure Test", CALCULATE (
DISTINCTCOUNT ( Table1[Invoice No] ),
FILTER (
Table1,
SUM ( Table1[Value] ) >= 1000
&& SUM ( Table1[Value] ) <= 2000
&& Table1[Product Code] IN { "101", "102" }
)
)
)
Proud to be a Super User!
@Anonymous
Hi,
@vanessafvg ‘s formula is wonderful. I just add a few parts. Try this measure please. This can be used in a card visual.
Measure = COUNTROWS ( FILTER ( SUMMARIZE ( Table1, Table1[Invoice No], "Measure Test", CALCULATE ( DISTINCTCOUNT ( Table1[Invoice No] ), FILTER ( Table1, SUM ( Table1[Value] ) >= 1000 && SUM ( Table1[Value] ) <= 2000 && Table1[Product Code] IN { 101, 102 } ) ) ), [Measure Test] = 1 ) )
Best Regards!
Dale
@Anonymous
Hi,
@vanessafvg ‘s formula is wonderful. I just add a few parts. Try this measure please. This can be used in a card visual.
Measure = COUNTROWS ( FILTER ( SUMMARIZE ( Table1, Table1[Invoice No], "Measure Test", CALCULATE ( DISTINCTCOUNT ( Table1[Invoice No] ), FILTER ( Table1, SUM ( Table1[Value] ) >= 1000 && SUM ( Table1[Value] ) <= 2000 && Table1[Product Code] IN { 101, 102 } ) ) ), [Measure Test] = 1 ) )
Best Regards!
Dale
@vanessafvg & @v-jiascu-msft Thank you very much to both of you!
Your support is highly appreciated.
@Anonymous
Proud to be a Super User!
Dear @vanessafvg,
From your function, I get blank as my output. But they don't give any error for this. I can't recognize the mistake.
Please help
Thank you
@Anonymous please post how you wrote your measure from what i gave you? also please post screenshot of results
Proud to be a Super User!
@Anonymous and if you place it in your table?
Proud to be a Super User!
@Anonymous from what you can see the measure does actually work however it expects some sort of context,
for example if you place it on a matrix you would see that the value woudl be summed by invoice number and measure test would show 1 and 1 for invoice 50 and 54
so to use it on a card, i think you actually need to summarise this in a table
try this
Table Test =
SUMMARIZE (
Table1,
Table1[Invoice No],
"Measure Test", CALCULATE (
DISTINCTCOUNT ( Table1[Invoice No] ),
FILTER (
Table1,
SUM ( Table1[Value] ) >= 1000
&& SUM ( Table1[Value] ) <= 2000
&& Table1[Product Code] IN { "101", "102" }
)
)
)
Proud to be a Super User!