Create an axis description from a measure

Question

I have an issue I cant figure out how to resolve&nbsp;Take this data as an example&nbsp;House No&nbsp; &nbsp; &nbsp; Total99&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;199&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1101 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1101 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1101 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1333 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1333 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1333 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1333 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1635 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1635 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1635 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1635 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp;If you do the usual measure SUM(Total) you would get&nbsp;&nbsp;House No&nbsp; &nbsp; &nbsp; Total99&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2101 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3333 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4635 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp;But I dont want the visual to show the above. I want thisSo the axis is 2 'Records', 3 'Records' etc and the value is the number of house numbers in each categoryI have a year filter so I want to be able to filter on year too.How do I go about doing this in DAX?&nbsp;&nbsp;

cmcmahan · Answer

The easiest way is to probably create a summary table and use that.&nbsp;
&nbsp;
Here's an example of somebody who needed to do something very similar.&nbsp; Try some of these solutions and let us know how it goes:
https://community.powerbi.com/t5/Desktop/Frequency-calc-Histogram/td-p/247320

v-lid-msft · Answer

Hi&nbsp;DebbieE&nbsp;,&nbsp;Firstly of all, because the measure can not be X-Axis, so we create a calculated table, which contain all the possible value of X-Axis:&nbsp;XaxisTale = 
GENERATESERIES (
    0,
    MAXX (
        SUMMARIZECOLUMNS ( 'Table'[HouseNo], "Sum", SUM ( 'Table'[Total] ) ),
        [Sum]
    ),
    1
)&nbsp;&nbsp;Then we can create a measure to be the value field of chart:&nbsp;SumTotalCount = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            DISTINCT ( 'Table'[HouseNo] ),
            "Sum", CALCULATE ( SUM ( 'Table'[Total] ), 'Table'[HouseNo] = EARLIER ( [HouseNo] ) )
        ),
        [Sum] IN FILTERS ( 'XaxisTale'[Value] )
    )
)&nbsp;If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.BTW, pbix&nbsp;as attached.&nbsp;Best regards,Community Support Team _ Dong LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-lid-msft · Answer

Hi DebbieE&nbsp;,How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?&nbsp;Best regards,Community Support Team _ Dong LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ashish_mathur · Answer

Hi,
You may download my PBI file from here.
Hope this helps.

debbiee · Answer

Thanks for both pbix files.&nbsp;I managed to get it working to a point but my report uses a slicer on the dim date table so I added a few more things. the model I ended up with was&nbsp;&nbsp;I tried numerous things before this. But when you added the slicer the numbers still came out the same. In the end I went and added the yearly information straight into the fact table in my SQL SP which works perfectly and you dont need to clutter your model up with the extra tables&nbsp;If I hadnt needed to add slicers on other dimensions this would have worked through

Forum Discussion

Create an axis description from a measure

6 Replies

Related content

Need help creating a measure

Help to create DAX Measure

Create count measure

Creating new measure, only showing other measures

Create Measure to Return Highest Value