Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi ,I'm trying to vizulize a trend chart line from a table includes of 4 columns (Date (datetime),Sold(0,1),SaleType(retail/wholesale),Inspected(Yes/No)
Axis : Date
Legend:SaleType
Values:Sold
slicer:Inspected
I'm trying to visualize the the percentage of Sold by Saletype in % of Total during date range that I can do this part
My problem is when I apply filter of Inspected I'm trying to see how many Percent of Saletype is No/Yes inspected as of total sale I'm keep getting the Inspected percentage as of total of itself not from the total .
Solved! Go to Solution.
@Anonymous In values, instead of using 'Sold' try a new measure:
Measure = DIVIDE ( [Sold] , CALCULATE( [Sold], ALL() ) )
That will give you the total % even when you filter for inspected.
If you use ALLSELECTED instead of ALL you'll get closer to what you're getting now.
Hope that helps? I think I understood your question correctly...
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Try this measure:
Measure =
DIVIDE(
[TotalSold],
CALCULATE(
[TotalSold],
ALLSELECTED ( Sheet1[Date] ),
REMOVEFILTERS ( Sheet1[Inspection] ),
REMOVEFILTERS ( Sheet1[SaleType] )
)
)
You also have an issue where your date is a text field so isn't sorting in correct order.
I separated things into dimensions and tidied it up which is available here:
Try this measure:
Measure =
DIVIDE(
[TotalSold],
CALCULATE(
[TotalSold],
ALLSELECTED ( Sheet1[Date] ),
REMOVEFILTERS ( Sheet1[Inspection] ),
REMOVEFILTERS ( Sheet1[SaleType] )
)
)
You also have an issue where your date is a text field so isn't sorting in correct order.
I separated things into dimensions and tidied it up which is available here:
@Anonymous In values, instead of using 'Sold' try a new measure:
Measure = DIVIDE ( [Sold] , CALCULATE( [Sold], ALL() ) )
That will give you the total % even when you filter for inspected.
If you use ALLSELECTED instead of ALL you'll get closer to what you're getting now.
Hope that helps? I think I understood your question correctly...
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@AllisonKennedy Thanks for the answer The formula gave me the following visualization the total as sold matches but when I apply filter as an inspection yes or no that does not match the table
Here's what numbers are like as a pivottable by count:
I need to display the following table as a percentage at the top
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |