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.
Hello, I am looking for some direction on excluding blank measure values from my average cost per square foot calculation. I am not using AVERAGE, because I have to do the cost per sqft calculation first. See example below. The desired result would be for the total average to be 10.00, but the Division 1/Product A Janurary thru June months w/no data, is causing the total average to be 7.50 instead. This is all driven by the date slicer. If the slicer is changed to 7/1/2020 thru 12/31/2020, then it works as ecpected with a total average of 10.00. The below is a simplified version of my actual data and I have the pbix for the example below, I'm just not sure how to upload or link it. I provided the simple table of data, along with my calculated columns and measures.
Any help would be greatly appreciated,
Division | Product | SQFT | YearMonth | Cost |
Division 1 | ProductA | 10 | 2020 Jul | 100 |
Division 1 | ProductA | 10 | 2020 Aug | 100 |
Division 1 | ProductA | 10 | 2020 Sep | 100 |
Division 1 | ProductA | 10 | 2020 Oct | 100 |
Division 1 | ProductA | 10 | 2020 Nov | 100 |
Division 1 | ProductA | 10 | 2020 Dec | 100 |
Division 2 | ProductB | 10 | 2020 Jan | 100 |
Division 2 | ProductB | 10 | 2020 Feb | 100 |
Division 2 | ProductB | 10 | 2020 Mar | 100 |
Division 2 | ProductB | 10 | 2020 Apr | 100 |
Division 2 | ProductB | 10 | 2020 May | 100 |
Division 2 | ProductB | 10 | 2020 Jun | 100 |
Division 2 | ProductB | 10 | 2020 Jul | 100 |
Division 2 | ProductB | 10 | 2020 Aug | 100 |
Division 2 | ProductB | 10 | 2020 Sep | 100 |
Division 2 | ProductB | 10 | 2020 Oct | 100 |
Division 2 | ProductB | 10 | 2020 Nov | 100 |
Division 2 | ProductB | 10 | 2020 Dec | 100 |
Calulated columns and measures:
HI @curtismob,
Did Ashish_Mathur's formula help for your scenario? If this is a case, you can consider accepting his suggestion to help others who faced a similar requirement to find it more quickly.
If not, you can feel free to post here with detailed descriptions to help us clarify your scenario.
Regards,
Xiaoxin Sheng
Hi,
Upload your PBI file to Google Drive and share the download link here.
Ashish,
Thank you for responding, please see link below.
https://drive.google.com/file/d/1v01ZpHo2XRy3LJSODzDEsOfXlF2iYsZp/view?usp=sharing
Hi,
You may download my PBI file from here.
Hope this helps.
Thanks again for the response. I used very simple data for my example, possibly too simple. Unfortunately, if the data is changed by adding another product in each divison and both cost and square feet numbers are changed,so that all cost per sqft doesn't come out to 10.00, then the suggested changes do not appear to work.
Hi,
How would you expect me to help you if you do not share a representative dataset? Share a realistic dataset and show the expected result there.
I apologize for the simplified data. Below are links to my version with updated data and your version with the updated data.
Ashish version:
https://drive.google.com/file/d/1CkvrqfaEoJv-ac-3cE4I8y8ehs-P-EvB/view?usp=sharing
Curtis version:
https://drive.google.com/file/d/1b_WtRzddQylXohYGQV3aM8EEdpxsI4Yz/view?usp=sharing
Hi,
I receive an access desnied message when i click on those links. On any dataset that you share, please show the expected result.
Hi,
In my version of the file, the average is 25.28. That seems correct. Drag the other 2 measures to the visual and export the data to MS Excel. Add the Total cost and Total Area column and divide the 2. The result will be 25.28.
Please recall my original post:
It does. Please recheck.
Thanks for your efforts on this.
Hi @curtismob,
I think this may be related to your measure formula who setting the 0 in divide functions. You can add an if statement to check the current row count to confirm they not blank.
Regards,
Xiaoxin Sheng
Thank you for the response. Can you provide an "if" statement example using the measures I provide initially? Everything I have tried doesn't appear to work.
Thank you,
Curtis
HI @curtismob,
You can try to use the following measure formula, I added the if statement to check hierarchy level and write a formula for the calculation on measure total level:
_mAvg Cost Per SQFT per Product =
VAR detailLevel =
CALCULATE (
DIVIDE (
DIVIDE ( [_mCost Per SQFT], [_mDistinctCount YYYMM], 0 ),
DISTINCTCOUNT ( [Product] ),
0
)
)
VAR totalLevel =
DIVIDE (
CALCULATE (
SUM ( 'CostPerSqft'[_cCost Per SQFT] ),
ALLSELECTED ( 'CostPerSqft' )
),
SUMX (
SUMMARIZE (
CostPerSqft,
[Area],
[Division],
[Product],
"DC", DISTINCTCOUNT ( CostPerSqft[_cYYYY Mon] )
),
[DC]
),
0
)
RETURN
IF (
ISINSCOPE ( CostPerSqft[_cYYYY Mon] ),
detailLevel,
IF ( ISINSCOPE ( CostPerSqft[Division] ), detailLevel, totalLevel )
)
Regards,
Xiaoxin Sheng
@curtismob , This formula seems fine, remove 0 from first divide and check
CALCULATE(DIVIDE(DIVIDE([_mCost Per SQFT],[_mDistinctCount YYYMM]), DISTINCTCOUNT('CostPerSqft'[Product]), 0))
@amitchandak, thank you for the response. Unforrtunately, that did not resolve my issue.
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 |