Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Ultimately, I am wanting AVERAGE sum of win per month divided by average number of open tables per month
I have a T-SQL routine that works swimmingly where I figure the average win MTD by summing for the month, then dividing by the number of days in the month. I then divide by the average number of open tables during the month and then my visualizer slices by game type (Blackjack, Craps etc.)
I need to implement the previously describe login in a measure, or measures, but I am receiving a BLANK using the following manor.
I am wanting avage win, month-to-date, per table game.
I have the following …
MEASER: win_MTD =
CALCULATE(Sum(TG_DataFrom_FLASH[win]), DATESMTD(GamingDatesList[GamingDatesList]))
MEASER: avgWinMTD =
CALCULATE(DIVIDE([win_MTD], Sum(GamingDatesList[DayNum])))
MEASURE: WinPerUnitDay =
CALCULATE(DIVIDE(SUM(TG_DataFrom_FLASH[win]), sum(TG_DataFrom_FLASH[TableCount])))
***********************************************************************************************
Main MEASURE: WPUMTD =
VAR WinUnit_MTD = CALCULATE(Sum([WinPerUnitDay]), DATESMTD(TGDataFromFlashRecap[GamingDate].[Date]))
VAR AvgWinUnit = CALCULATE(DIVIDE(WinUnit_MTD, DATESMTD(TGDataFromFlashRecap[GamingDate].[Date])))
VAR TableCountMTD = CALCULATE(SUM(TG_DataFrom_FLASH[TableCount]), DATESMTD(GamingDatesList[GamingDatesList]))
VAR avgTableCountMTD = CALCULATE(DIVIDE(TableCountMTD, sum(GamingDatesList[DayNum]),0))
VAR finaloutput = CALCULATE( DIVIDE(AvgWinUnit, avgTableCountMTD))
RETURN finaloutput
******************************************************************************************************
Solved! Go to Solution.
Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.
Need help uploading data? click here
Want faster answers? click here
Hi @ES335Cherry ,
Could you please share the sample data for the accurate answer.Provide sample data that covers your issue or question in a usable format .
Thank you.
Hi @ES335Cherry ,
Thank you for the prompt response @FBergamaschi !
We totally understand and appreciate the data sensitivity. If possible, even a mocked-up version with fake numbers (but same structure) would help us understand the layout and relationships.
Please provide sample data that covers your issue or question in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community
Thank you.
Regards,
pallavi.
Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.
Need help uploading data? click here
Want faster answers? click here
FBergamaschi,
I am giving you credit for the resolution as it steered me in the direction of finding the problem. There were gaps in records due to data entry which caused blanks at many dates. I should have included code in my DAX to prevent missing data from causing that level of issue(s).
- Doug
I understand the need for the requested data but the gaming commission would never sing off on posting or even providing that information privately. Obfuscating the actual numbers may scew the evalution and would not be helpful. Thanx anyway.
- 335
No problem, point is that the DAX you wrote is not trivial and, without a model view, it is impossible to help you, we need to see the visual settings, the columns grouped and the model
I remain at disposal
Incidentally, I have tried placing default return values of 0 for when divide by zero might be encountered. I also filtered out any zero values from the stored procedure that gathers the records for my BI report.
- Doug (es335 cherry)
User | Count |
---|---|
25 | |
12 | |
8 | |
7 | |
7 |
User | Count |
---|---|
25 | |
12 | |
11 | |
10 | |
6 |