Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ES335Cherry
Frequent Visitor

Measure is returning BLANK

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

******************************************************************************************************

1 ACCEPTED SOLUTION
FBergamaschi
Solution Sage
Solution Sage

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

View solution in original post

7 REPLIES 7
v-pagayam-msft
Community Support
Community Support

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.

v-pagayam-msft
Community Support
Community Support

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.

FBergamaschi
Solution Sage
Solution Sage

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

FBergamaschi ,

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

ES335Cherry
Frequent Visitor

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) 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.