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 'sales' table and a 'dates' table.
The dates table contains :
- Date (from jan2016 until dec 2020)
- month-day
- month-workday
- month
- year
...etc
For simplicity, the sales table contains:
- date
- business unit
- order number
- net sales amount
Note that there can be many orders per day and these can be all in the same or another business unit.
I have a filter on month and a filter on Business unit.
I made a double direction relation from sales[date] >> datetable[date]. I think this is correct, because i want to be able to show e.g number of orders for a certain month or business unit only and therefore i need the bidirectional filter.
Now i have a table that shows me, when i select a certain business unit and month, the sales per month day.
However, some small business unit could sell only every 5 days. So you would get in the table something like:
Day 1: 500
Day 6: 850
Day 11: 1350
...
etc
However, i would like to see also that the other days are 0. See the example table below, that has no value for month day 8.
(Note that it does show 2x value = 0 , but this is due to rounding as they are in fact not 0)
/
Next to this, i have a question on the running total.
[RunningSales]] = CALCULATE(DIVIDE(sum(Sales[Sales)]);1000);FILTER(ALLSELECTED(sales[Day in month]);sales[Day in month]<=max(sales[Day in month*])))
The running total works like a charm. But i have three things i dont like.
I have been messing around with all this REALLY long now and i get frustrated.
Do i miss a certain concept? (very likely)
Hope my questions are clear.
Best regards
DP
Solved! Go to Solution.
Hi @denpries,
In the following visualization, you only want to display OCT NOV DEC month value's, and don't show part in black box, right? If it is, add [Month] column in visual level filter, let [Month] is not equal to Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep. Check if it works fine.
Best Regards,
Angelia
Hi @denpries,
For the first question, @Greg_Deckler have answered it correctly.
For second question, I am still confusing, please share more detailed information for further analysis.
For third issue, we can't break line where the value is 0. Please review this feature request and vote it. You can also create an idea in this website.
Best Regards,
Angelia
In the Values area for your visualization, you should be able to click on the drop down for you numeric field and choose "Show items with no data", that may fix your issue. By default 0's or blanks are not displayed in tables and matrices. If that doesn't work, often you can do a CONCATENATE with "" to turn 0's to text. Also, in a table or matrix, you can include a column or measure that always has a value and then those rows will show up.
Unfortunately, its not working this way in my case.
I would like to show this using a few images but i dont see a way to upload them.
This is what i do now:
Sales in k = DIVIDE(sumx(FILTER(TABLE;TABLE[Workday in month]<[someworkday]);TABLE[SALES]);1000)
This measure of course results into a value. Dont be distracted by the filter though: it just selects a subsection of the full table.
Row headers:
Day in month* = if(TABLE[currentdate].[Date]>TODAY();day(today());TABLE[currentdate].[Day])
Column header is the month label
This gives me a matrix with sales per month-day (vertical) and month label (horizontal) but only when the workday is before a certain set someworkday. If i have a slicer and select only 3 months, i will see OKT NOV DEC.
If there are no items at all with sales at 15th of OKT, this cell is BLANK.
Doing e.g. +0 to the whole sales formula works, but then also all non-sliced months show up in the table, since of course, the measure result is blank due to the filter context, but you force it to 0 with the +0.
Messing around with "" is - in my opinion - a bit off. Its a measure, we dont want to force it into a text measure, right? Plus it has the same result as adding +0.
So what i think about what should happen (but not quite too much into into the dax yet...) is that you want to only add a +0 for outcomes in the filtered context. Not to everything...
Hi @denpries,
First, you can load the images by click the "Photos" button.
Second, I can still confusing about your requirement and problem, could you please share your .pbix file for further analysis?
Best Regards,
Angelia
Hope this brings some clarity. I cannot copy the PBX as it contains classified information unfortunately.
I could create a phony document, but hope the stuff below clears up manners.
Left table:
Workday in month = calculated column. Every row represents a sales entry with date, this sets the workday
Workday in month = LOOKUPVALUE(Date2Workday[MonthWorkday];Date2Workday[Datum];if(ZANALYSIS_PATTERN[currentdate].[Date]>TODAY();today();ZANALYSIS_PATTERN[currentdate].[Date]))
This gives me back only those rows in the table where workday < current workday.
Net Sales** in k = DIVIDE(sumx(FILTER(ZANALYSIS_PATTERN;ZANALYSIS_PATTERN[Workday in month]<[currentworkday]);ZANALYSIS_PATTERN[Net Sales (excl. pd)]);1000)
With fiscal month on the legend, the left matrix is made.
Similar set up for right, cumulative table:
[Running net sales (excl. pd) [wkdy]]] = if(max(ZANALYSIS_PATTERN[Workday in month])>=[currentworkday];BLANK();1000*CALCULATE([Net Sales** in k];FILTER(ALLSELECTED(ZANALYSIS_PATTERN[Workday in month]);ZANALYSIS_PATTERN[Workday in month]<=max(ZANALYSIS_PATTERN[Workday in month]))))
And it works! Butt.... a lot of blanks. I want to return a 0, but ONLY if it is within filter scope!
If i would do the following for example:
Net Sales** in k = DIVIDE(sumx(FILTER(ZANALYSIS_PATTERN;ZANALYSIS_PATTERN[Workday in month]<[currentworkday]);ZANALYSIS_PATTERN[Net Sales (excl. pd)])+0;1000)
(the +0 solution). Note, the +0 at the end gives similar results
I get almost what i want, except all 0 for non filtered months as well ( i have a filter on OKT NOV DEC)
Hi @denpries,
In the following visualization, you only want to display OCT NOV DEC month value's, and don't show part in black box, right? If it is, add [Month] column in visual level filter, let [Month] is not equal to Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep. Check if it works fine.
Best Regards,
Angelia
That works, of course.
But, its not a good solution for my problem. The reason for this is that i use a slicer to select the months to be seen.
Fiddling in visual filters is not really what i want to achieve.
Maybe its just not possible yet. Im thinking of solutions where you want to replace BLANK but ONLY when in filter context. Hmm
Hi @denpries,
Aside from the solution I poested, I can't figure out other better way to do it. I will post update if there is new idea.
Thanks,
Angelia
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |