Create an axis description from a measure

[DebbieE]

I have an issue I cant figure out how to resolve

Take this data as an example

House No      Total

99                   1

99                   1

101                  1

101                  1

101                  1

333                  1

333                  1

333                  1

333                  1

635                  1

635                  1

635                  1

635                  1

If you do the usual measure SUM(Total) you would get 

House No      Total

99                   2

101                  3

333                  4

635                  4

But I dont want the visual to show the above. I want this

So the axis is 2 'Records', 3 'Records' etc and the value is the number of house numbers in each category

I have a year filter so I want to be able to filter on year too.

How do I go about doing this in DAX? 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[DebbieE]

Thanks for both pbix files.

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

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

If I hadnt needed to add slicers on other dimensions this would have worked through

[v-lid-msft]

Hi @DebbieE ,

If you are use slicer, the summary calculated table or calculated column will not work, because the calculated columns are computed during the database processing and then stored in the model. 

But we are using measure is our solution, it should be dynamic, there is no need to create the summary table.

If it doesn't meet your requirement, Please show the exact expected result based on the Tables that you have shared.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft]

Hi @DebbieE ,

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?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft]

Hi @DebbieE ,

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:

XaxisTale = 
GENERATESERIES (
    0,
    MAXX (
        SUMMARIZECOLUMNS ( 'Table'[HouseNo], "Sum", SUM ( 'Table'[Total] ) ),
        [Sum]
    ),
    1
)

Then we can create a measure to be the value field of chart:

SumTotalCount = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            DISTINCT ( 'Table'[HouseNo] ),
            "Sum", CALCULATE ( SUM ( 'Table'[Total] ), 'Table'[HouseNo] = EARLIER ( [HouseNo] ) )
        ),
        [Sum] IN FILTERS ( 'XaxisTale'[Value] )
    )
)

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 as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Cmcmahan]

The easiest way is to probably create a summary table and use that. 

Here's an example of somebody who needed to do something very similar.  Try some of these solutions and let us know how it goes:

https://community.powerbi.com/t5/Desktop/Frequency-calc-Histogram/td-p/247320