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.
Dear Friends,
I have a below set of data.
Date | Outlet | Invoice # |
01-Nov-2020 | 12435452 | 123 |
02-Nov-2020 | 12435452 | 124 |
03-Nov-2020 | 12435452 | 125 |
03-Nov-2020 | 12435452 | 127 |
01-Nov-2020 | 53435454 | 765 |
02-Nov-2020 | 53435454 | 766 |
03-Nov-2020 | 53435454 | 777 |
03-Nov-2020 | 53435454 | 780 |
I want to check the outletwise invoice number incremnt whether it is increased by 1 or not. Ultimately i want to find the result like the below one.
Date | Outlet | Invoice # | Invoice # |
01-Nov-2020 | 12435452 | 123 | First Invoice number |
02-Nov-2020 | 12435452 | 124 | Incresed By 1 |
03-Nov-2020 | 12435452 | 125 | Incresed By 1 |
03-Nov-2020 | 12435452 | 127 | Not Incresed By 1 |
01-Nov-2020 | 53435454 | 765 | First Invoice number |
02-Nov-2020 | 53435454 | 766 | Incresed By 1 |
03-Nov-2020 | 53435454 | 777 | Not Incresed By 1 |
03-Nov-2020 | 53435454 | 780 | Not Incresed By 1 |
@amitchauhan @amitchandak @jazzk @v-easonf-msft @speedramps
Solved! Go to Solution.
Hi @Jeevan1991 ,
A first thing - please remove a summarization for the Rank column:
Second thing - please add Dense to Rank function as a last argument.
Rank =
RANKX (
FILTER (
DAILY_BILLWISE_SALES,
DAILY_BILLWISE_SALES[CAFEID] = EARLIER ( DAILY_BILLWISE_SALES[CAFEID] )
),
DAILY_BILLWISE_SALES[BILLNO],
,
ASC,
Dense
)
The result:
pbix file: https://gofile.io/d/phJrox
_______________
If I helped, please accept the solution and give kudos! 😀
Hi Bro,
The above method giving me inconsistent results.
For an example look at the below screenshot where i have filtered just one cafe.
Even though "BillNo" 102 is the first bill number it is showinng the rank as 21, because of this invoice Order too showing wrongly.
Column Used is =
Please help me on this bro.
Hi @Jeevan1991 ,
You can use calculated column and measure to achieve this:
Calculated column:
Rank =
RANKX (
FILTER (
'Table',
'Table'[Outlet] = EARLIER ( 'Table'[Outlet] )
),
'Table'[Invoice #],
,
ASC
)
Measure:
Invoice Order =
VAR __PreviousNumber = MAX('Table'[Rank]) - 1
VAR __Diff =
MAX('Table'[Invoice #]) -
CALCULATE(
MAX('Table'[Invoice #]), FILTER(ALL('Table'), 'Table'[Outlet] = MAX('Table'[Outlet]) && 'Table'[Rank] = __PreviousNumber))
RETURN
SWITCH(MAX('Table'[Rank]),
1, "First Invoice number",
SWITCH(__Diff,
1, "Incresed By 1",
"Not Incresed By 1"
)
)
The result:
_______________
If I helped, please accept the solution and give kudos! 😀
Please find the file here:
_______________
If I helped, please accept the solution and give kudos! 😀
Bro,
For me RANK is giving me different result.
For used is.
Do you have the same value for Rank in the Data View?
Could you please share your pbix file?
_______________
If I helped, please accept the solution and give kudos! 😀
Hi @Jeevan1991 ,
A first thing - please remove a summarization for the Rank column:
Second thing - please add Dense to Rank function as a last argument.
Rank =
RANKX (
FILTER (
DAILY_BILLWISE_SALES,
DAILY_BILLWISE_SALES[CAFEID] = EARLIER ( DAILY_BILLWISE_SALES[CAFEID] )
),
DAILY_BILLWISE_SALES[BILLNO],
,
ASC,
Dense
)
The result:
pbix file: https://gofile.io/d/phJrox
_______________
If I helped, please accept the solution and give kudos! 😀
Hi Bro,
The above method giving me inconsistent results.
For an example look at the below screenshot where i have filtered just one cafe.
Even though "BillNo" 102 is the first bill number it is showinng the rank as 21, because of this invoice Order too showing wrongly.
Column Used is =
Please help me on this bro.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |